Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

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 DateDime...
  • Greg_Deckler's avatar
    Greg_Deckler
    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:

    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