Forum Discussion

CanadaMGB's avatar
CanadaMGB
Frequent Visitor
9 years ago
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 ...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    9 years ago

    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]))

     

v-qiuyu-msft's avatar
v-qiuyu-msft
Icon for Community Support rankCommunity Support
9 years ago

Hi CanadaMGB,

 

The sample table you share doesn't have column you put in matrix column group: "New","ADDS",..etc. Please share complete data used by matrix which can be copied rather than screenshot. It would be better if you can share pbix file. 

 

Best Regards,
Qiuyun Yu 

CanadaMGB's avatar
CanadaMGB
Frequent Visitor
9 years ago

Thanks for replying v-qiuyu-msft . What I showed in the first image was the complete data set (I'm also using a date table). This is just an example I set up that isolated the problem I'm having difficulty conceptualizing.

 

The measures I'm using to illustrate the matrix on the right are as follows:

 

TOTSALES = SUM('Summary Transactions'[Sales])

PRIORSALES = CALCULATE(Sum('Summary Transactions'[Sales]), PREVIOUSMONTH(Dates[Date]))

NEW = IF (ISBLANK([PRIORSALES]) = TRUE, [TOTSALES], 0)

ADDS = IF( [TOTSALES] - [PRIORSALES] > 0, [TOTSALES] - [PRIORSALES] - [NEW], 0 )

LOSS = IF( [TOTSALES] - [PRIORSALES] < 0, [TOTSALES] - [PRIORSALES], 0 )

CHANGE = [NEW] + [ADDS] + [LOSS]