how to calculate the spend of last month

[FisherShi]

Hi everyone,

my data has 3 fields:

    [Load Month] contains 1/1/2021, 2/1/2021,3/1/2021, 4/1/2021, 5/1/2021 with  date format like "MM/DD/YYYY"

    [Total Cost Gross] spend of each item

    [Item] 

i want to compare the spend of this month vs. last month. i can use Sum({1<[Load Month] = {'$(=Date(max([Load Month])))'}>} [Total Cost Gross]) to get the spend of this month, how can i calculate the spend of last month?

i tried to make the expression like below, however it doesn't work. Can anyone please help? thanks you very much.

Sum({1<[Load Month] = {'$(=Date(max([Load Month])-32))'}>} [Total Cost Gross])

Regards,

Fisher

[MayilVahanan · Accepted solution]

HI @FisherShi 

Try like below

Sum({1<[Load Month] = {"$(=Date(AddMonths(max([Load Month]),-1),'MM/DD/YYYY'))"}>} [Total Cost Gross])

[FisherShi]

Thanks Mayil. it works😁

have a nice day.

Regards,

Fisher