Count Within Date Range

[mrouton]

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.

[Greg_Deckler]

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

[mrouton]

NPSR table

Item_Num	Date_Received
1	6/2/2016
2	6/2/2016
3	6/2/2016
4	6/16/2016
5	6/16/2016
6	7/1/2016
7	7/1/2016
8	7/4/2016
9	7/4/2016

DateDimension Table

DateKey	WeekStart	WeekStartYear	StartQtrYear	StartMonthYear
6/1/2016	5/29/2016	2016	2016 - Q2	May, 2016
6/2/2016	5/29/2016	2016	2016 - Q2	May, 2016
6/3/2016	5/29/2016	2016	2016 - Q2	May, 2016
6/4/2016	5/29/2016	2016	2016 - Q2	May, 2016
6/5/2016	6/5/2016	2016	2016 - Q2	June, 2016
6/6/2016	6/5/2016	2016	2016 - Q2	June, 2016
6/7/2016	6/5/2016	2016	2016 - Q2	June, 2016
6/8/2016	6/5/2016	2016	2016 - Q2	June, 2016
6/9/2016	6/5/2016	2016	2016 - Q2	June, 2016
6/10/2016	6/5/2016	2016	2016 - Q2	June, 2016
6/11/2016	6/5/2016	2016	2016 - Q2	June, 2016
6/12/2016	6/12/2016	2016	2016 - Q2	June, 2016
6/13/2016	6/12/2016	2016	2016 - Q2	June, 2016
6/14/2016	6/12/2016	2016	2016 - Q2	June, 2016
6/15/2016	6/12/2016	2016	2016 - Q2	June, 2016
6/16/2016	6/12/2016	2016	2016 - Q2	June, 2016
6/17/2016	6/12/2016	2016	2016 - Q2	June, 2016
6/18/2016	6/12/2016	2016	2016 - Q2	June, 2016
6/19/2016	6/19/2016	2016	2016 - Q2	June, 2016
6/20/2016	6/19/2016	2016	2016 - Q2	June, 2016
6/21/2016	6/19/2016	2016	2016 - Q2	June, 2016
6/22/2016	6/19/2016	2016	2016 - Q2	June, 2016
6/23/2016	6/19/2016	2016	2016 - Q2	June, 2016
6/24/2016	6/19/2016	2016	2016 - Q2	June, 2016
6/25/2016	6/19/2016	2016	2016 - Q2	June, 2016
6/26/2016	6/26/2016	2016	2016 - Q2	June, 2016
6/27/2016	6/26/2016	2016	2016 - Q2	June, 2016
6/28/2016	6/26/2016	2016	2016 - Q2	June, 2016
6/29/2016	6/26/2016	2016	2016 - Q2	June, 2016
6/30/2016	6/26/2016	2016	2016 - Q2	June, 2016
7/1/2016	6/26/2016	2016	2016 - Q2	June, 2016
7/2/2016	6/26/2016	2016	2016 - Q2	June, 2016
7/3/2016	7/3/2016	2016	2016 - Q3	July, 2016
7/4/2016	7/3/2016	2016	2016 - Q3	July, 2016
7/5/2016	7/3/2016	2016	2016 - Q3	July, 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!

[Greg_Deckler · 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

[mrouton]

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.  

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