Difference between column criteria base on calculated table

Question

Hi all,&nbsp;I created a calculuated table from my data model with the following syntax:&nbsp;Test Table = SUMMARIZECOLUMNS
(OrderIntakeTable[Time_stamp],
OrderIntakeTable[Season],
"Total Ordered",SUM(OrderIntakeTable[Ordered quantity]))Below result from the calculated table:&nbsp;&nbsp;Time_stampAW 2017AW 2018SS 2018SS 2019Grand Total2018.201,688,3731,864,7912,162,954962,2976,678,4152018.211,688,3731,860,8672,167,3591,012,1526,728,7512018.221,688,3731,860,6272,167,5391,017,6416,734,1802018.231,685,4841,858,2912,167,6011,030,8616,742,2372018.241,685,4841,858,2752,166,3231,077,1606,787,2422018.251,685,4841,857,9492,166,1961,078,0696,787,698&nbsp;How do I find the difference between each row Grand Total to get below output on column Differences in PowerBI?&nbsp;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.&nbsp;Time_stampAW 2017AW 2018SS 2018SS 2019Grand TotalDifferences2018.201,688,3731,864,7912,162,954962,2976,678,41502018.211,688,3731,860,8672,167,3591,012,1526,728,75150,3362018.221,688,3731,860,6272,167,5391,017,6416,734,1805,292018.231,685,4841,858,2912,167,6011,030,8616,742,2378,0572018.241,685,4841,858,2752,166,3231,077,1606,787,24245,0052018.251,685,4841,857,9492,166,1961,078,0696,787,698456&nbsp;Appreciate all the help!&nbsp;RegardsHidayat

v-jiascu-msft · Answer

Hi&nbsp;Hidayat,
&nbsp;
Try this formula please.
Column =
VAR currentTimeStamp = [Time_stamp]
VAR lastTimeStamp =
    CALCULATE (
        MAX ( Table1[Time_stamp] ),
        FILTER ( 'Table1', Table1[Time_stamp] &lt; currentTimeStamp )
    )
RETURN
    IF (
        ISBLANK ( lastTimeStamp ),
        0,
        [Grand Total]
            - CALCULATE (
                SUM ( Table1[Grand Total] ),
                FILTER ( Table1, Table1[Time_stamp] = lastTimeStamp )
            )
    )

&nbsp;
Best Regards,
Dale

anonymous · Answer

Hi Dale,&nbsp;The dax work, but it's not the desired output.&nbsp;My data is repeated on rows instead of column.&nbsp;&nbsp;When displayed on matrix visual, it did not give the correct calculation.&nbsp;RegardsHidayat

v-jiascu-msft · Answer

Hi&nbsp;Hidayat,
&nbsp;
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&nbsp;data first.
&nbsp;
Best Regards,
Dale

anonymous · Answer

Hi Dale,&nbsp;Sorry about that, could have better phrase it on the original post.Yes, it is still a calculated table.&nbsp;Below link to file for reference.&nbsp;File Link&nbsp;Best Regards,Hidayat&nbsp;

v-jiascu-msft · Answer

Hi&nbsp;Hidayat,
&nbsp;
Try this one please.&nbsp;
Difference 2 =
VAR currentTimeStamp = [Time_stamp]
VAR lastTimeStamp =
    CALCULATE (
        MAX ( 'Test Table'[Time_stamp] ),
        FILTER ( 'Test Table', 'Test Table'[Time_stamp] &lt; 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 )
            )
    )

&nbsp;
Best Regards,
Dale

Forum Discussion

Difference between column criteria base on calculated table

9 Replies

Related content

Date difference with multiple criteria

Dynamic Calculation Different Period

Different Calculations

Calculate SUM with Multiple Criteria

Calculating Differences

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