Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
4 years ago
Solved

Return last data for each month

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's avatar
    Anonymous
    4 years ago

    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.

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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.