Forum Discussion
Solved
Components of Change vs. Prior Period
Hoping someone has a solution to this one, I'm stumped as to how to approach it. Here is a very basic example. I have sales by month for 3 different clients. What I need to do is ...
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,
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.