accumulated value

[Ayden]

hello all I am tryin to get accumulated value for previous month and current month.

my use case is when a user selects a specific month, i need the KPI to show accumulated value for the selected month and another Kpi to show the accumulated value of the previous month, and 3rd KPI shows the Delta off the two KPI (KPI 1- KPI2) depending on the users selection.

I have tried  sum( aggr( rangesum( above( sum([Balance Amount]),0,24) ),Month)) also tried RangeSum(Above(Sum({<Month={'$(vPriorMonth)'}>}[Balance Amount]),0,rowno(total))).

I am able to get current month numbers correct, but once i make a selection i.e select a specific month my numbers are off. Also without making a selection the prior month numbers are off.

Any help are welcome.

Thanks

Guys.

[rubenmarin]

HI, you say it's for a KPI but that expression with Above() is used on tables or charts. where do you want to show it?

In a KPI you'll only need: Sum({<Month={'$(vPriorMonth)'}>}[Balance Amount])

[Ayden]

thanks for responding to my msg.

this is an example of what I want 

Part                                          July                              August                      Difference

1223445                              265267.27                419437.40              124,170.13

4455667                                                                    100,231                  100,231.53

3334455                             21,609.47                   36,870.24               15,260.77

the above amount for July and August are accumulated values. I want the users to have the ability to select any month and it would give them the accumulated value for the month selected and previous month and the differences in values of the selected month and previous months value.

Thanks in advance.

[rubenmarin · Accepted solution]

You can use 3 expressions:

Previous month Acc: Sum({<Month={"<=$(=Max(Month)-1)"}>}[Balance Amount])

Current month Acc: Sum({<Month={"<=$(=Max(Month))"}>}[Balance Amount])

Difference: Use a substraction of the other two or: Sum({<Month={"=$(=Max(Month))"}>}[Balance Amount])

Maybe you need a field with the month stored as number for this to work.

Or use a date field with:

Previous month Acc: Sum({<Date={"<$(=MonthStart(Max(Date)))"}>}[Balance Amount])

Current month Acc: Sum({<Date={"<$(=MonthStart(Max(Date),1))"}>}[Balance Amount])