How to calculate the difference between columns in matrix

[mnsen]

Hello,
This is a sample of data:

and these are the desired results:

This can be accomplished by creating a matrix with the following options:

Rows = Type

Columns Year & Period

Values: sum(#)

I need to add a column (diff) that shows the difference between each individual column and the previous column (and the difference between totals as well).

Please let me if you have any questions or need any further information 

I really appreciate your help on this one

Thanks in advance

[mnsen]

I almost found a solution for this one: 

As @amitchandak suggested, I created an index column here is the data including the index

Then using this measure to calculate the difference from this thread (https://community.powerbi.com/t5/Desktop/Calculate-the-difference-between-two-columns-without-hard-c... )

Diff = 
VAR currIndex = MAX ( Data[Index] )
VAR currValue =
    CALCULATE (
        SUM ( Data[#] ),
        FILTER ( ALLSELECTED( Data ), Data[Index] = currIndex),
        VALUES ( Data[Type])
    )
VAR preValue =
    CALCULATE (
        SUM ( Data[#] ),
        FILTER ( ALLSELECTED( Data ), Data[Index] = currIndex -1),
        VALUES ( Data[Type])
    )
RETURN
    IF ( preValue <> BLANK (),currValue - preValue, "-" )

Here is the results:

This is almost what I am looking for, however, if we apply a filter on the period, it does not work:

This is because they are not consecutive periods ( it works fine if they are consecutive periods).

I tried to modify the measure to solve this issue:

It actually works fine when the filter is applied:

But it does not work when the filter is removed:

As you can see, it calcuate the fieernce between a certain column and the sum of all previous columns 

So, Is there any way to modify the measure to solve this issue (respecting the filter applied).

Thank you in advance.

[amitchandak]

@mnsen , Create a new table year and year period and then create a rank on period

New columns in period table

Period Rank = RANKX(all('Period'),'Period'[year period],,ASC,Dense)

measure
This Period= CALCULATE(sum('order'[Qty]), FILTER(ALL('Period'),'Period'[Period Rank]=max('Period'[Period Rank])))
Last Period= CALCULATE(sum('order'[Qty]), FILTER(ALL('Period'),'Period'[Period Rank]=max('Period'[Period Rank])-1))

diff = [This Period] -[Last Period]

The same approach I discussed in my Week Blog

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...

[mnsen]

Hi @amitchandak 
Thank you for your reply. unfortunately, the solution didnot work:

This is the new table including the rank on the period: