Forum Discussion
Components of Change vs. Prior Period
Hi,
This is the DAX formula for the increase
=if(HASONEVALUE(Table1[Client]),if([Value]-CALCULATE([Value],PREVIOUSMONTH(Table1[Date]))>0,[Value]-CALCULATE([Value],PREVIOUSMONTH(Table1[Date])),BLANK()),SUMX(SUMMARIZE(Table1,[Client],Table1[Date],"ABCD",if([Value]-CALCULATE([Value],PREVIOUSMONTH(Table1[Date]))>0,[Value]-CALCULATE([Value],PREVIOUSMONTH(Table1[Date])),BLANK())),[ABCD]))
This is the DAX formula for the decrease
=if(HASONEVALUE(Table1[Client]),if([Value]-CALCULATE([Value],PREVIOUSMONTH(Table1[Date]))<0,[Value]-CALCULATE([Value],PREVIOUSMONTH(Table1[Date])),BLANK()),SUMX(SUMMARIZE(Table1,[Client],Table1[Date],"ABCD",if([Value]-CALCULATE([Value],PREVIOUSMONTH(Table1[Date]))<0,[Value]-CALCULATE([Value],PREVIOUSMONTH(Table1[Date])),BLANK())),[ABCD]))
Hi,
Please share the link from where we can download your file. Also, show your expected result.
Ashish_Mathur I don't believe the PBIX file from my screen shots will help at all, it was just an example to illustrate where I'm stuck.
How about this image from excel. I have a list of sales amounts by month for 2 different clients. What I want to do is calculate the per client change over the previous month, seperate that change as to whether its an increase or a decrease, and then sum the resulting values.
Hi,
This is the DAX formula for the increase
=if(HASONEVALUE(Table1[Client]),if([Value]-CALCULATE([Value],PREVIOUSMONTH(Table1[Date]))>0,[Value]-CALCULATE([Value],PREVIOUSMONTH(Table1[Date])),BLANK()),SUMX(SUMMARIZE(Table1,[Client],Table1[Date],"ABCD",if([Value]-CALCULATE([Value],PREVIOUSMONTH(Table1[Date]))>0,[Value]-CALCULATE([Value],PREVIOUSMONTH(Table1[Date])),BLANK())),[ABCD]))
This is the DAX formula for the decrease
=if(HASONEVALUE(Table1[Client]),if([Value]-CALCULATE([Value],PREVIOUSMONTH(Table1[Date]))<0,[Value]-CALCULATE([Value],PREVIOUSMONTH(Table1[Date])),BLANK()),SUMX(SUMMARIZE(Table1,[Client],Table1[Date],"ABCD",if([Value]-CALCULATE([Value],PREVIOUSMONTH(Table1[Date]))<0,[Value]-CALCULATE([Value],PREVIOUSMONTH(Table1[Date])),BLANK())),[ABCD]))
Thanks Ashish_Mathur that works. As for my first example (and my actual problem), I think the combination of your explanation + this posting below using power query will get me where I need to be. Much appreciated.
You are welcome.