Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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])
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])
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.
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])