DAX Dates Between Question

• vivekkam

  SSC Enthusiast

  Points: 122

  More actions

  January 29, 2016 at 3:08 pm

  #323987

  Hi,

  Scenario:

  Publications have pageviews which are recorded over a period of time. The Web Activity tables stores the page views and it's associated to the activity date table based on the date key. On the other end, publication have a release date which is associated to the publication release date based on the date key. The Publication and Web Activity tables are related to each other using the publication key.

  Goal:

  To find the number of page views for publication over a specific date range where the start date should be the publication release date and end date should be 7 days ahead of the publication release date. Basically I want to find out the weekly page views for publication starting from its release date.

  Below is the SQL code which gives me the correct results as expected but when I try to do it via DAX I get errors.

  SQL Code:

  SELECT

  [Title],

  SUM(PageViews) as CCPageViews

  FROM [marketing].[Publication] P

  inner join marketing.ContentAnalysisWeb cc on

  p.ID_Publication = cc.ID_Publication

  inner join marketing.ContentAnalysisDate cadate on

  cc.ID_Date_Recorded = cadate.ID_Date

  inner join marketing.PublicationReleaseDate PR

  on p.ID_Date_Release = PR.ID_Date

  WHERE cadate.Date between dateadd(month,0,pr.date) and dateadd(day,6,pr.Date)

  group by Title

  order by Title

  DAX Code:

  7 Days:=CALCULATE

  (

  SUM('Web Activity'[PageViews]),

  DATESBETWEEN('Activity Date'[Date],DATEADD('Publication Release Date'[Date],0,DAY),DATEADD('Publication Release Date'[Date],6,DAY))

  )

  When I execute the dax code its unable to recognize the unique publication release date for each publication and passed a set of values which is not unique to the specific publication. Any idea what I might be doing wrong here ?

  In SQL when I specify this WHERE clause:

  WHERE cadate.Date between dateadd(month,0,pr.date) and dateadd(day,6,pr.Date)

  it passes row by row unique release date corresponding to the publication but it doesn't seems to be the case in DAX. Why is it so ? Any help would be much appreciated.