Count Based on Date in the same Table

[Neqom]

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()

[v-huijiey-msft]

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]

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.

[v-huijiey-msft · Accepted solution]

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!

[Awm]

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
)

[Neqom]

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