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 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.

Need Help
  • 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

     

     

     

     

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    9 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      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!

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        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