Return last data for each month

[Syndicate_Admin]

Hello!!

I have a table where every week they upload some data, and to compare the end of the month, I would need that in the whole page where I show the statistics, there is only the last data of each month of each game ... It's possible?

Thanks a lot!

[Anonymous · Accepted solution]

Hi @Syndicate_Admin 

Create a calendar table, relate calendar table with your data table by date columns and then create measures.

Calendar table:

Date = ADDCOLUMNS(CALENDARAUTO(),"Year",YEAR([Date]),"Month",MONTH([Date]),"YearMonth",YEAR([Date])*100+MONTH([Date]))

Create a calculated column in Data table.

YearMonth = RELATED('Date'[YearMonth])

Measures:

Last Date each Month & Game = 
CALCULATE(MAX('Table'[Date]),FILTER(ALL('Table'),'Table'[YearMonth] = MAX('Table'[YearMonth])&&'Table'[Game] = MAX('Table'[Game])))

M_Value = CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Date] = [Last Date each Month & Game]))

Result:

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.