Forum Discussion

Nijlal01's avatar
Nijlal01
Helper I
5 years ago
Solved

Total as difference on multiple levels

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
)

 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

7 Replies