Last date of collected data for each month

[Jared Hagerman]

We are collecting data every Friday of every week. Our goal is to show the monthly counts by using the last date of data for each month. The issue I'm running into is trying to figure out how to calculate that a date is the last one for each month/year combination so this can run without further updates.

For example, we have collection dates of 3/19/21 and 3/26/21. I need to label those with the 3/26/21 as the "last" of the month and be able to do so for each month of every year moving forward to show trending data.

Is there a formula that will do this

[Paul Shumaker]

I would personally include a calculated column that parses out the month/year combination you're looking for. Assume a new column outputting 3/21:

Concatenate((DatePart('month', [Date]), '/', DatePart('year', [Date]))

Then, create a column that acts as a flag. We'll just call this column Flag:

CASE WHEN [Date] = max([Date]) over intersect(whatever columns you wish to group by)THEN "Last"ELSE NULLEND

Finally, in the visualization you wish to show these counts, you can data limit using an expression:

[Flag] is not null

 

[Fabian Duerr]

Please 1st create some helper columns for month and year.

Then check for the max of your datecolumn per month/year.

Year([YourDate])

Month([YourDate])

Max([YourDate]) OVER ([Year],[Month])