Forum Discussion

Neqom's avatar
Neqom
Frequent Visitor
2 years ago
Solved

Count Based on Date in the same Table

I have a Table with the below structure:

ID - Date

1 - 1/Jan/2020
1 - 2/Jan/2020
2 - 1/Jan/2020
3 - 1/Jan/2020
4 - 5/Jan/2020
...

Where ID appears multiple times. I need a measure in a Table Visual where a new column called "Last  30 days" shows the amount of entries of the table in the last 30 days

ID - Amount
1 - 2
2 - 1
3 - 1
4 - 1

Important to note that I can't add a filter to the visual since the visual contains more data. Like ID that have no entries in the last 30 days or other information that if I add the filter will be remove from the visual. Also the full visual has information from multiple tables.

I need something like Count(ID) where Date+30>=Today()

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Neqom ,

    Here's my modified data, otherwise unchanged.

     

    Just select Show item with no data in the ID field and now, IDs with no counts in the last 30 days show blank.

     

    If you have any other questions please feel free to contact me.

     

    Best Regards,
    Yang
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Neqom ,

     

    Thanks for the reply from Awm .

     

    First, create a measure to store your definition of today=2020/1/10:

    _today = DATE(2020,1,10)

     

    Then, create a measure to count the number of rows:

    Count = CALCULATE(
        COUNTROWS('Table'),
        FILTER(
            'Table'.
            'Table'[Date] >= [_today] - 30 && 'Table'[Date] < [_today]
        )
    )

     

    The page visualization is shown below:

     

    If you want to calculate the last 30 days of data for today (2024/05/07), you can replace _today with Today().

    Count = CALCULATE(
        COUNTROWS('Table'),
        FILTER(
            'Table',
            'Table'[Date] >= TODAY() - 30 && 'Table'[Date] < TODAY()
        )
    )

     

    The pbix file is attached.

     

    If you have any other questions please feel free to contact me.

     

    Best Regards,
    Yang
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    • Neqom's avatar
      Neqom
      Frequent Visitor
      2 years ago

      Hello!
      Thanks for your help.
      Iy works. however it shows blank instead of "0" for ID where there is nothing in the last 30 days
      Sorry for no specify that at the beginning.


      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi Neqom ,

        Here's my modified data, otherwise unchanged.

         

        Just select Show item with no data in the ID field and now, IDs with no counts in the last 30 days show blank.

         

        If you have any other questions please feel free to contact me.

         

        Best Regards,
        Yang
        Community Support Team

         

        If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
        If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

  • Neqom's avatar
    Neqom
    Frequent Visitor
    2 years ago

    Note* Example asuming that Today = 10/Jan/2020

  • Awm's avatar
    Awm
    Advocate I
    2 years ago

    Last 30 Days =
    VAR MaxDate = MAX('YourTable'[Date])
    VAR MinDate = MAX('YourTable'[Date]) - 30
    RETURN
    CALCULATE(
    COUNTROWS('YourTable'[Date]),
    'YourTable'[Date] >= MinDate && 'YourTable'[Date] <= MaxDate
    )