Difference between column criteria base on calculated table

[Anonymous]

Hi all,

I created a calculuated table from my data model with the following syntax:

Test Table = SUMMARIZECOLUMNS
(OrderIntakeTable[Time_stamp],
OrderIntakeTable[Season],
"Total Ordered",SUM(OrderIntakeTable[Ordered quantity]))

Below result from the calculated table:

Time_stamp	AW 2017	AW 2018	SS 2018	SS 2019	Grand Total
2018.20	1,688,373	1,864,791	2,162,954	962,297	6,678,415
2018.21	1,688,373	1,860,867	2,167,359	1,012,152	6,728,751
2018.22	1,688,373	1,860,627	2,167,539	1,017,641	6,734,180
2018.23	1,685,484	1,858,291	2,167,601	1,030,861	6,742,237
2018.24	1,685,484	1,858,275	2,166,323	1,077,160	6,787,242
2018.25	1,685,484	1,857,949	2,166,196	1,078,069	6,787,698

How do I find the difference between each row Grand Total to get below output on column Differences in PowerBI?

Calculation logic: Row 2 - Row 1, Row 3 - Row 2, etc...

If it's in excel I could just use cell reference to calculate the difference.

Time_stamp	AW 2017	AW 2018	SS 2018	SS 2019	Grand Total	Differences
2018.20	1,688,373	1,864,791	2,162,954	962,297	6,678,415	0
2018.21	1,688,373	1,860,867	2,167,359	1,012,152	6,728,751	50,336
2018.22	1,688,373	1,860,627	2,167,539	1,017,641	6,734,180	5,29
2018.23	1,685,484	1,858,291	2,167,601	1,030,861	6,742,237	8,057
2018.24	1,685,484	1,858,275	2,166,323	1,077,160	6,787,242	45,005
2018.25	1,685,484	1,857,949	2,166,196	1,078,069	6,787,698	456

Appreciate all the help!

Regards

Hidayat

[v-jiascu-msft]

Hi Hidayat,

Try this formula please.

Column =
VAR currentTimeStamp = [Time_stamp]
VAR lastTimeStamp =
    CALCULATE (
        MAX ( Table1[Time_stamp] ),
        FILTER ( 'Table1', Table1[Time_stamp] < currentTimeStamp )
    )
RETURN
    IF (
        ISBLANK ( lastTimeStamp ),
        0,
        [Grand Total]
            - CALCULATE (
                SUM ( Table1[Grand Total] ),
                FILTER ( Table1, Table1[Time_stamp] = lastTimeStamp )
            )
    )

Best Regards,

Dale

[Anonymous]

Hi Dale,

The dax work, but it's not the desired output.

My data is repeated on rows instead of column.

When displayed on matrix visual, it did not give the correct calculation.

Regards

Hidayat

[v-jiascu-msft]

Hi Hidayat,

This one isn't the one in your first post. Is this one still a calculated table? Can you share the file? Mask the sensitive data first.

Best Regards,

Dale

[Anonymous]

Hi Dale,

Sorry about that, could have better phrase it on the original post.

Yes, it is still a calculated table.

Below link to file for reference.

File Link

Best Regards,

Hidayat

[v-jiascu-msft]

Hi Hidayat,

Try this one please. 

Difference 2 =
VAR currentTimeStamp = [Time_stamp]
VAR lastTimeStamp =
    CALCULATE (
        MAX ( 'Test Table'[Time_stamp] ),
        FILTER ( 'Test Table', 'Test Table'[Time_stamp] < currentTimeStamp )
    )
RETURN
    IF (
        ISBLANK ( lastTimeStamp ),
        0,
        CALCULATE (
            SUM ( [Total Ordered] ),
            ALLEXCEPT ( 'Test Table', 'Test Table'[Time_stamp] )
        )
            - CALCULATE (
                SUM ( 'Test Table'[Total Ordered] ),
                FILTER ( 'Test Table', 'Test Table'[Time_stamp] = lastTimeStamp )
            )
    )

Best Regards,

Dale

[Anonymous]

Hi Dale,

The output is still not correctly calculating.

Regards

Hidayat

[v-jiascu-msft]

Hi Hidayat,

Change the aggregation type to others like "Average" or use a measure instead. Please give it a try.

Difference 3 =
VAR lastTimeStamp =
    CALCULATE (
        MAX ( 'Test Table'[Time_stamp] ),
        FILTER (
            ALL ( 'Test Table' ),
            'Test Table'[Time_stamp] < MIN ( 'Test Table'[Time_stamp] )
        )
    )
RETURN
    IF (
        ISBLANK ( lastTimeStamp ),
        0,
        SUM ( [Total Ordered] )
            - CALCULATE (
                SUM ( 'Test Table'[Total Ordered] ),
                FILTER ( ALL ( 'Test Table' ), 'Test Table'[Time_stamp] = lastTimeStamp )
            )
    )

Best Regards,

Dale

[Anonymous]

Hi Dale,

The measure "Difference 3" works! 

If i were to add column criteria "Season", how do i create a measure to calculate the difference base on this?

Regards

Hidayat

[v-jiascu-msft]

Hi Hidayat,

Try this one which is more compatible.

Difference 4 =
VAR lastTimeStamp =
    CALCULATE (
        MAX ( 'Test Table'[Time_stamp] ),
        FILTER (
            ALL ( 'Test Table' ),
            'Test Table'[Time_stamp] < MIN ( 'Test Table'[Time_stamp] )
        )
    )
RETURN
    IF (
        ISBLANK ( lastTimeStamp ),
        0,
        SUM ( [Total Ordered] )
            - CALCULATE (
                SUM ( 'Test Table'[Total Ordered] ),
                FILTER (
                    ALL ( 'Test Table'[Time_stamp] ),
                    'Test Table'[Time_stamp] = lastTimeStamp
                )
            )
    )

Best Regards,

Dale