Components of Change vs. Prior Period

[CanadaMGB]

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 visualize the components that make up the per-company change vs. the prior year, so whether that change is result of new business, expansion of existing business, or loss of business.  

In the image below, the context of the matrix to the right gives me the desired amounts per client. What I can't seem to get my head around is how to carry that context over, and aggregate those per-client amounts into a grand total by month, so that the matrix & graph on the left return the sum of the amounts on the matrix to the right.

Much Appreciated.

[Ashish_Mathur]

Hi,

Please share the link from where we can download your file.  Also, show your expected result.

[CanadaMGB]

@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. 

[Ashish_Mathur · Accepted solution]

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

[CanadaMGB]

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. 

https://community.powerbi.com/t5/Desktop/Power-Query-previous-row-value-based-on-criteria-O/m-p/1258...

[Ashish_Mathur]

You are welcome.

[v-qiuyu-msft]

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]

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]