Forum Discussion
Count Within Date Range
- 9 years ago
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:
- WeekStartYear
- StartQtrYear
- StartMonthYear
- 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
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:
- WeekStartYear
- StartQtrYear
- StartMonthYear
- 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
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. :smileysurprised:
If I still lived in Cbus, I'd definitely look into the user group.