Calculate sum of amounts with a date filter

[phaum1967]

Hi everyone,

This is my table:

I would like to have only sum of amount depending on  (Type could be actual, budget, forectast,..), on GL account (which is not visible in my screen shot) for the last actual date.

I try this:

LastPerAAMM:=CONCATENATE(YEAR([LastPeriod]);CONCATENATE("-";FORMAT(Month([LastPeriod]);"00")))

to find last month info (formated AAAA-MM)

and sum like that:

LastPeriod$:=CALCULATE(sum(Data[Montant]);filter(Data;Data[AAA-MM]=[LastPerAAMM]))

Result is that LastPeriod$ is empty.

I tried this:

LastPeriod:=CALCULATE(Max(Data[Date]);Data[Type]="actual") 

with this:

LastPeriod$:=CALCULATE(sum([Montant]);filter(Data;Data[Date]=[LastPeriod]))

LastPeriod$ equal sum of all months like it does not take into account filter...

thanks for helping 🙂

[v-xuding-msft · Accepted solution]

Hi @phaum1967 ,

By my test, the following is my sample you can have a try. And the data is not exactly the same as yours. I add the column of GL and the type of budget.

I create a calculated column of LastPerAAMM.

LastPerAAMM = CONCATENATE(YEAR([LastPeriod]),CONCATENATE("-",FORMAT(Month([LastPeriod]),"00")))

Measure = CALCULATE(SUM(Table1[Amount]),ALLEXCEPT(Table1,Table1[GL],Table1[Type],Table1[LastPerAAMM]))

If the result is not what you want, please post the expected output.

Best Regards,

Xue Ding

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