Forum Discussion

STC-EDR's avatar
STC-EDR
New Member
1 year ago
Solved

Help on DAX to compute differences for multiple variables

Hi, I am struggling to come up with a DAX solution to compute the differences of multiple selected forecast versions.

 

First, I will select two forecast versions on my slicers, which will be shown across 12 months in a table. 

 

Next, I want to derive the difference between the two versions by months as a comparison in the last line:

 

 

 

 

My current dataset is modelled as follows.
The column under Version contains columns consisting from Apr-24 to Mar-25 sequentially, and period runs from JAN to DEC:



 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Thanks and appreciate any help gathered. 

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    1 year ago

    Hi,

    I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

    Please check the below picture and the attached pbix file.

     

     

     

     

    INDEX function (DAX) - DAX | Microsoft Learn

     

    expected result measure: = 
VAR _diff =
    CALCULATE (
        SUM ( forecast[Value] ),
        INDEX (
            1,
            ALLSELECTED ( forecast[Version], forecast[Version sort] ),
            ORDERBY ( forecast[Version sort], ASC )
        )
    )
        - CALCULATE (
            SUM ( forecast[Value] ),
            INDEX (
                1,
                ALLSELECTED ( forecast[Version], forecast[Version sort] ),
                ORDERBY ( forecast[Version sort], DESC )
            )
        )
VAR _result =
    CALCULATE (
        SUM ( forecast[Value] ),
        KEEPFILTERS ( forecast[Version] IN VALUES ( 'version'[Version] ) )
    )
RETURN
    IF (
        HASONEVALUE ( 'version'[Version] ),
        SWITCH (
            TRUE (),
            COUNTROWS ( VALUES ( forecast[Version] ) ) = 2, IF ( SELECTEDVALUE ( 'version'[Version] ) = "Difference", _diff, _result )
        )
    )

     

1 Reply

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    1 year ago

    Hi,

    I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

    Please check the below picture and the attached pbix file.

     

     

     

     

    INDEX function (DAX) - DAX | Microsoft Learn

     

    expected result measure: = 
VAR _diff =
    CALCULATE (
        SUM ( forecast[Value] ),
        INDEX (
            1,
            ALLSELECTED ( forecast[Version], forecast[Version sort] ),
            ORDERBY ( forecast[Version sort], ASC )
        )
    )
        - CALCULATE (
            SUM ( forecast[Value] ),
            INDEX (
                1,
                ALLSELECTED ( forecast[Version], forecast[Version sort] ),
                ORDERBY ( forecast[Version sort], DESC )
            )
        )
VAR _result =
    CALCULATE (
        SUM ( forecast[Value] ),
        KEEPFILTERS ( forecast[Version] IN VALUES ( 'version'[Version] ) )
    )
RETURN
    IF (
        HASONEVALUE ( 'version'[Version] ),
        SWITCH (
            TRUE (),
            COUNTROWS ( VALUES ( forecast[Version] ) ) = 2, IF ( SELECTEDVALUE ( 'version'[Version] ) = "Difference", _diff, _result )
        )
    )