how to generate a table to calculate the sum of the last 6 months?

[yanzhao_LBS]

for example I have a table

name	month	Amount
A	2022-2	22
A	2022-3	30
A	2022-5	22
A	2022-7	30
A	2022-9	40
A	2022-10	18
B	2022-3	40
B	2022-4	12
B	2022-6	40
B	2022-7	22
B	2022-8	66
B	2022-10	45

I want to generate a new table to calculate like this(today is Nov,so the last 6 month should be from 2022-5 to 2022-10)

Name	last 6 month amount
A	110
B	173

And the data should be refreshed as time goes by.

Can someone help?

Thanks.

[VahidDM · Accepted solution]

Hi @yanzhao_LBS 

Try the following code to create your new table:

Table 2 =
VAR _current_Date =
    TODAY ()
VAR _End_Of_Last_Month =
    EOMONTH ( _current_Date, -1 )
VAR _Start_of_5_monthago =
    EOMONTH ( _End_Of_Last_Month, -6 )
RETURN
    SUMMARIZE (
        FILTER (
            'Table',
            'Table'[month] <= _End_Of_Last_Month
                && 'Table'[month] > _Start_of_5_monthago
        ),
        [name],
        "last 6 month amount", SUM ( 'Table'[Amount] )
    )

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

Appreciate your Kudos!! 

LinkedIn | Twitter | Blog | YouTube 

[yanzhao_LBS]

Thanks for your help,it works perfectly