Count Data In Most Recent Month Only

[bradlocke]

I have a spreadsheet that has data similar to below. This data always goes back 24 months and is updated every month. I have been trying to write an KPI expression that counts the number of certificates that occurred during the most recent month with a secondary KPI that shows the difference, up or down, from the month before. March 2021 in this example, so my output would be 4 for the primary KPI. My current expression gives me a KPI output of "1" presumably because there is only 1 occurrence for the most recent date, 03/13/2021. I need all occurrences for the whole month. Here is my current expression for the primary KPI. I have searched and tried everything I could find before asking. Can anyone assist? Thanks! Also due to agency restrictions, I am unable to download and open qvw files.

Count({<SiteName-={'STATEWIDE'},Certificate={'Beginners','Intermediate','Masters'},Date = {"$(=Date(Max(Date)))"}>}Certificate)

[Vegar · Accepted solution]

You need to define an interval for your date modifier. Try something like this.

Count({<SiteName-={'STATEWIDE'},Certificate={'Beginners','Intermediate','Masters'},Date = {">=$(=monthstart(Max(Date)))<=$(=monthend(max(Date)))"}>}Certificate)

[bradlocke]

Thank you! This worked perfectly.