Calculation of difference between two rows

[SchmidMatt84]

Hi

I need to calcualte the difference between two values which is different depending on a "version"-Value. If its the version "Basis" the difference should be calculated bewtween the years (eg. 2026-2025), if its "V1"-"V2" the calculation should compare it to the "Basis" (eg. V1 - Basis). The differnce should be calculated accoring to this logic:

I've tried several approaches, including an index (Difference). In a Dax-formula I've tried with ALL an FILTER to get the values for the "Difference-Index". But ist not really working within the filter context of a mtraix table, which includes drill-downs with other attributes.

Any tipps would be appreciated!

[FBergamaschi]

Hi

Please include a small rows subset (not an image) of all the tables involved in your request, so that we can import them in Power BI and reproduce the data model (please show it).  Please include also the DAX you wrote so far and the result you get. In this way we can reproduce the scenario and help you. Thank you

[SchmidMatt84]

Hi

I've included a small subset ot the data an the relevant master data here:

https://www.dropbox.com/scl/fi/mlbbcw6llvfalgjqqq9gb/Extract_Fabric_Test.xlsx?rlkey=vciia80vy17w64qg...

The data model looks like this:

[FBergamaschi]

My solution with measures

Wert Value = SUM ('10_Total_Merge'[Wert] ) this is the measure I am referring

Wert Delta =

VAR _Variante = SELECTEDVALUE( '90_Version'[Variante] )

VAR CurrentYear = SELECTEDVALUE( '90_Version'[Jahr] )

VAR MinYear = CALCULATE( MIN ( '90_Version'[Jahr] ), REMOVEFILTERS( ) )

RETURN

IF (

      _Variante = "Basis" && CurrentYear <> MinYear && ISINSCOPE( '90_Version'[Jahr] ),

      [Wert Value]- CALCULATE ( [Wert Value], '90_Version'[Jahr] = CurrentYear - 1 ),

      [Wert Value] - CALCULATE ( [Wert Value], '90_Version'[Variante] = "Basis" )

)

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your threadconsider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[FBergamaschi · Accepted solution]

I think a visual calculation here would be the best approach anyway so I try to solve it immediately using the names in the visual picture you sent

Difference Visual CALC =
VAR MinYear = MINX ( ROWS, [Jahr] )

VAR CurrentYear = [Jahr]
RETURN

IF (
      [Variante] = "Basis" && CurrentYear <> MinYear && ISATLEVEL ( [Jahr] ), 

      [DVE]- CALCULATE ( [DVE], [Jahr] = CurrentYear -1 ),
      [DVE] - CALCULATE ( [DVE], [Variante] = "Basis" )
)

If you need to reuse this concept you need a regular measure, in that case refer to my previous post to provide some data

If this helped, please consider giving kudos and mark as a solution

@mein replies or I'll lose your threadconsider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[SchmidMatt84]

Hi, thank you a lot. This worked well applied to a measure.

However, it was a bit more complicated with the field parameter. The only solution I found was this one. There are only 3 values, but its still a bit clumsy...

Wert Delta2 =

VAR _Variante = SELECTEDVALUE('10_Total_Merge'[Variante] )

VAR CurrentYear = SELECTEDVALUE( '10_Total_Merge'[Jahr] )

VAR MinYear = CALCULATE( MIN ( '10_Total_Merge'[Jahr] ), REMOVEFILTERS( ) )

VAR __SelectedValue =

    SELECTCOLUMNS (

        SUMMARIZE ( Auswahl_Kategorie, Auswahl_Kategorie[Auswahl_Kategorie], Auswahl_Kategorie[Auswahl_Kategorie Felder] ),

        Auswahl_Kategorie[Auswahl_Kategorie]

        )

RETURN

Switch(

    TRUE()

    ,__SelectedValue = "Pkm",

        IF (

            _Variante = "Basis" && CurrentYear <> MinYear,

            [Pkm] - CALCULATE ( [Pkm], '10_Total_Merge'[Jahr] = CurrentYear -1 ),

            [Pkm] - CALCULATE ( [Pkm], '10_Total_Merge'[Variante] = "Basis" ))

    ,__SelectedValue = "PVE",

        IF (

            _Variante = "Basis" && CurrentYear <> MinYear,

            [PVE] - CALCULATE ( [PVE], '10_Total_Merge'[Jahr] = CurrentYear -1 ),

            [PVE] - CALCULATE ( [PVE], '10_Total_Merge'[Variante] = "Basis" ))

    ,__SelectedValue = "Yield",

        IF (

            _Variante = "Basis" && CurrentYear <> MinYear,

            [Yield] - CALCULATE ( [Yield], '10_Total_Merge'[Jahr] = CurrentYear -1 ),

            [Yield] - CALCULATE ( [Yield], '10_Total_Merge'[Variante] = "Basis" )

))

[SchmidMatt84]

Thanks, I've tried visual calculations as well. But that doesn't work in combination with field parameters? If there's a possibility to solve it with dax measures, that would be preferable...

[FBergamaschi]

Visual Calculation can work with Field Parameters but with some limitations, so it depends what you need to do. So I go back to the original reply:

Please include a small rows subset (not an image) of all the tables involved in your request, so that I can import them in Power BI and reproduce the data model (please show it).  Please include also the DAX you wrote so far and the result you get. In this way we can reproduce the scenario and help you (Incude the field parameter usage you are considering: are you using a field parameters to allow users choose columns, measures, both?) Thank you

[SchmidMatt84]

Hi. Thanks! I've inlcuded an extract from the data in my earlier response. I use field parameters to let the user select different measures. 

[FBergamaschi]

I replied with the measure code but in order to do more if this is not enough I need the pbix with the field parameter at work

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI