Total as difference on multiple levels

[Nijlal01]

Hi all,

I am trying to display the total as difference. Unfortunatly I do not get it to work on al levels. Anyone knows how to rewrite the DAX formula in order to display difference correctly?

KR,

Lars

Difference = 
VAR x = 
CALCULATE(
    SUM(Sheet1[QTY]),
    FILTER(
        ALLSELECTED(Sheet1),
        Sheet1[PublishDate] < MAX(Sheet1[PublishDate])
    )
)
VAR y = 
CALCULATE(
    SUM(Sheet1[QTY]),
    FILTER( ALL(Sheet1), Sheet1[PublishDate] = MAX(Sheet1[PublishDate]) ) 
)
RETURN
IF(
    HASONEFILTER(Sheet1[PublishDate]),
    SUM(Sheet1[QTY]),
    x-y
)

[Nijlal01]

I did try to use the post @Greg_Deckler. Unfortunatly I did not get it to work 😞 There are more post about displaying total as difference however not one which explains how to do it on multiple levels. And I am to newbie to figure it out myself.

[Greg_Deckler]

@Nijlal01 Can you post your sample data?

[Nijlal01]

Hi @Greg_Deckler ,

Thanks for the help! See data below:

PublishDate	Product	ShipTo	WkNr	QTY
1-1-2021	100001	A	1	10
1-1-2021	100001	B	1	15
1-1-2021	100001	B	2	10
1-1-2021	100002	A	1	15
1-1-2021	100002	A	2	20
1-1-2021	100002	B	2	20
2-1-2021	100001	A	1	15
2-1-2021	100001	A	1	25
2-1-2021	100001	B	2	20
2-1-2021	100002	B	2	15
2-1-2021	100002	A	2	30
2-1-2021	100002	A	1	10

[v-rzhou-msft · Accepted solution]

Hi @Nijlal01 

I have a test by your sample and dax code. I find that your use AllSELECTED and ALL function in your X and Y code. Due to you use hierachy level in your Matrix, I think you don't need to use these two function to get data. But you need to use ALL function to get Max Date.

Update Code:

Difference =
VAR _MaxDate =
    MAXX ( ALL ( Sheet1 ), Sheet1[PublishDate] )
VAR _x =
    CALCULATE (
        SUM ( Sheet1[QTY] ),
        FILTER ( Sheet1, Sheet1[PublishDate] < _MaxDate )
    )
VAR _y =
    CALCULATE (
        SUM ( Sheet1[QTY] ),
        FILTER ( Sheet1, Sheet1[PublishDate] = _MaxDate )
    )
RETURN
    IF ( HASONEFILTER ( Sheet1[PublishDate] ), SUM ( Sheet1[QTY] ), _x - _y )

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Nijlal01]

You are a genius! Thanks 😄

[Greg_Deckler]

@Nijlal01 See if MM3TR&R helps: Matrix Measure Total Triple Threat Rock & Roll - Microsoft Power BI Community

[Nijlal01]

Ouch, it breaks my head but I will try if I can make it work using that post