cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mrouton
Frequent Visitor

Count Within Date Range

I am having an issue with a measure where I'm trying to count the number of rows and then display the count in the visualization by Year, Quarter, Month, and Week.  I have 2 tables: NPSR and DateDimension.  DateDimension is a date table.

 

My formula is:  TotalCount = calculate(countrows(NPSR),filter(NPSR,([Date_Received]<=lastDate('DateDimension'[DateKey]) && [Date_Received]>firstdate(DateDimension[DateKey]))))

 

The Date_Received column is a date/time column and the DateKey column is also a date/time column.

 

In my DateDimension table, for each DateKey, I created columns to store the WeekStart date (the Sunday date the DateKey would correspond to), WeekStartYear (Year the WeekStart date corresponds to), StartQtrYear (YYYY - Q# the WeekStart date would correspond to), and StartMonthYear (Month, Year that the WeekStart date would correspond to).

 

I then try to create a line chart where the value is TotalCount.  For my axis, I have 4 that are columns in the DateDimension table in this order:  WeekStartYear, StartQtrYear, StartMonthYear, WeekStart.  However the visualization is blank.  Any help would be much appreciated.

1 ACCEPTED SOLUTION

OK, I imported your data as two Enter Data queries:

 

NPSR

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZcjBDQAQDAXQXf5ZUi2KWRr7r0FcGvWOzwyMBCUhyaxYySAxSox6g9WnfaNnOrFHjzFuVI/5xNo=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Item_Num = _t, Date_Received = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Item_Num", Int64.Type}, {"Date_Received", type date}})
in
    #"Changed Type"

DateDimension

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ndO9CsIwFIbhWymdI8k5+WtuQXBwLh0c3MTNoXevFmpTFanvlJeQZ/vS922yYtVJak0brZa5q6PZNUd99OE0mma6H8zTKXQeugBdnN9W+YXtb9fzyiXoMnQddAU6cRQKhbrApTdJj2XAMmKZsMxYdlhWA5JfH+tDqsNSsFQsPZYBy4hltaGlN8mMZYdlodI7JrMVCpXC14CqfHN+cpdx5QJ08X833AE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [DateKey = _t, WeekStart = _t, WeekStartYear = _t, StartQtrYear = _t, StartMonthYear = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DateKey", type date}, {"WeekStart", type date}, {"WeekStartYear", Int64.Type}, {"StartQtrYear", type text}, {"StartMonthYear", type date}})
in
    #"Changed Type"

I then created your exact measure:

TotalCount = calculate(countrows(NPSR),filter(NPSR,([Date_Received]<=lastDate('DateDimension'[DateKey]) && [Date_Received]>firstdate(DateDimension[DateKey]))))

I created a hierarchy:

  1. WeekStartYear
  2. StartQtrYear
  3. StartMonthYear
  4. WeekStart

I created a Line chart visualization:

Axis: Hierarchy from above (WeekStartYear Hierarchy)

Values: TotalCount

 

Seems to work like a champ although I do not agree with your Month level numbers, you have:

  • May - 3
  • June - 4
  • July - 2

 

 

 

 


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

4 REPLIES 4
Greg_Deckler
Super User IV
Super User IV

Can you provide some sample data, in text, that we could use to recreate the issue?


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




NPSR table

Item_NumDate_Received
16/2/2016
26/2/2016
36/2/2016
46/16/2016
56/16/2016
67/1/2016
77/1/2016
87/4/2016
97/4/2016

 

DateDimension Table

DateKeyWeekStartWeekStartYearStartQtrYearStartMonthYear
6/1/20165/29/201620162016 - Q2May, 2016
6/2/20165/29/201620162016 - Q2May, 2016
6/3/20165/29/201620162016 - Q2May, 2016
6/4/20165/29/201620162016 - Q2May, 2016
6/5/20166/5/201620162016 - Q2June, 2016
6/6/20166/5/201620162016 - Q2June, 2016
6/7/20166/5/201620162016 - Q2June, 2016
6/8/20166/5/201620162016 - Q2June, 2016
6/9/20166/5/201620162016 - Q2June, 2016
6/10/20166/5/201620162016 - Q2June, 2016
6/11/20166/5/201620162016 - Q2June, 2016
6/12/20166/12/201620162016 - Q2June, 2016
6/13/20166/12/201620162016 - Q2June, 2016
6/14/20166/12/201620162016 - Q2June, 2016
6/15/20166/12/201620162016 - Q2June, 2016
6/16/20166/12/201620162016 - Q2June, 2016
6/17/20166/12/201620162016 - Q2June, 2016
6/18/20166/12/201620162016 - Q2June, 2016
6/19/20166/19/201620162016 - Q2June, 2016
6/20/20166/19/201620162016 - Q2June, 2016
6/21/20166/19/201620162016 - Q2June, 2016
6/22/20166/19/201620162016 - Q2June, 2016
6/23/20166/19/201620162016 - Q2June, 2016
6/24/20166/19/201620162016 - Q2June, 2016
6/25/20166/19/201620162016 - Q2June, 2016
6/26/20166/26/201620162016 - Q2June, 2016
6/27/20166/26/201620162016 - Q2June, 2016
6/28/20166/26/201620162016 - Q2June, 2016
6/29/20166/26/201620162016 - Q2June, 2016
6/30/20166/26/201620162016 - Q2June, 2016
7/1/20166/26/201620162016 - Q2June, 2016
7/2/20166/26/201620162016 - Q2June, 2016
7/3/20167/3/201620162016 - Q3July, 2016
7/4/20167/3/201620162016 - Q3July, 2016
7/5/20167/3/201620162016 - Q3July, 2016

 

Based on the sample data above, my graph should show the following as I drill down to each level:

 

Year level

2016=9

 

Qtr level

Q2=7, Q3=2

 

Month level

June=7, July=2

 

Week level

5/29/16=3

6/12/16=2

6/26/16=2

7/3/16=2

 

Thanks in advance for your help!

OK, I imported your data as two Enter Data queries:

 

NPSR

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZcjBDQAQDAXQXf5ZUi2KWRr7r0FcGvWOzwyMBCUhyaxYySAxSox6g9WnfaNnOrFHjzFuVI/5xNo=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Item_Num = _t, Date_Received = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Item_Num", Int64.Type}, {"Date_Received", type date}})
in
    #"Changed Type"

DateDimension

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ndO9CsIwFIbhWymdI8k5+WtuQXBwLh0c3MTNoXevFmpTFanvlJeQZ/vS922yYtVJak0brZa5q6PZNUd99OE0mma6H8zTKXQeugBdnN9W+YXtb9fzyiXoMnQddAU6cRQKhbrApTdJj2XAMmKZsMxYdlhWA5JfH+tDqsNSsFQsPZYBy4hltaGlN8mMZYdlodI7JrMVCpXC14CqfHN+cpdx5QJ08X833AE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [DateKey = _t, WeekStart = _t, WeekStartYear = _t, StartQtrYear = _t, StartMonthYear = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DateKey", type date}, {"WeekStart", type date}, {"WeekStartYear", Int64.Type}, {"StartQtrYear", type text}, {"StartMonthYear", type date}})
in
    #"Changed Type"

I then created your exact measure:

TotalCount = calculate(countrows(NPSR),filter(NPSR,([Date_Received]<=lastDate('DateDimension'[DateKey]) && [Date_Received]>firstdate(DateDimension[DateKey]))))

I created a hierarchy:

  1. WeekStartYear
  2. StartQtrYear
  3. StartMonthYear
  4. WeekStart

I created a Line chart visualization:

Axis: Hierarchy from above (WeekStartYear Hierarchy)

Values: TotalCount

 

Seems to work like a champ although I do not agree with your Month level numbers, you have:

  • May - 3
  • June - 4
  • July - 2

 

 

 

 


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

So I found the problem.  It turns out that my NPSR table was linked to a Product table.  The Product table was also linked to another table which was linked to the DateDimenion table.  So I'm guessing that even though the NPSR table was not linked directly to the DateDimension table that the relationship to the DateDimension table still carried through to the NPSR table by following the chain of the other linked tables.  

 

And for whatever reason, I obviously can't count.  Smiley Surprised

 

If I still lived in Cbus, I'd definitely look into the user group.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors