comparison external interest between two scenarios

[LKBoldrino]

Hi everyone,

I'm working on a Power BI report where I need to calculate the external interest (Aussenzinsen) for two selected scenarios and then compute the delta between them. Here is the setup:

1. I have a table SzenarioKombinationen that contains different scenario groups.
2. I have two slicers on my report page named "Scenario 1" and "Scenario 2". Both slicers use the same column Szenariogruppe from the SzenarioKombinationen table.
3. I need to calculate the sum of external interest (Aussenzinsen) for the selected scenarios in each slicer and then calculate the difference (delta) between these two values.

   Here is a simplified version of my data model:

   • Table: STB_Monatswerte_MER_FIS

     • Columns: Stichtag, BucketDatum, Aussenzinsen, Szenario

     • Table: SzenarioKombinationen

       • Column: Szenariogruppe

         I have tried creating measures using SELECTEDVALUE and TREATAS, but I'm having trouble ensuring that each measure respects the correct slicer selection. Here is what I have so far for Scenario 1:

         MS_Aussenzinsen_Scenario1_Value =
         VAR _stichtag = SELECTEDVALUE(STB_Monatswerte_MER_FIS[Stichtag])
         VAR _year = YEAR(_stichtag)
         
         VAR _start_date =
             SWITCH(
                 SELECTEDVALUE(SlicerYears[Year]),
                 1, _stichtag,
                 2, DATE(_year + 1, 1, 1),
                 DATE(_year + 2, 1, 1)  // Default case
             )
         
         VAR _end_date =
             SWITCH(
                 SELECTEDVALUE(SlicerYears[Year]),
                 1, DATE(_year, 12, 31),
                 2, DATE(_year + 1, 12, 31),
                 DATE(_year + 2, 12, 31)  // Default case
             )
         
         VAR Szenario1 = SELECTEDVALUE('SzenarioKombinationen'[Szenariogruppe]) ?????
         
         VAR _sum_sen =
             CALCULATE(
                 SUM(STB_Monatswerte_MER_FIS[Aussenzinsen]),
                 STB_Monatswerte_MER_FIS[BucketDatum] > _start_date &&
                 STB_Monatswerte_MER_FIS[BucketDatum] <= _end_date &&
                 TREATAS({Szenario1}, STB_Monatswerte_MER_FIS[Szenario])
             ) / 1000
         
         RETURN _sum_sen

         How can I ensure that Power BI correctly distinguishes between the first slicer (Scenario 1) and the second slicer (Scenario 2) when calculating these measures?

         Thank you in advance for your help!

         

[LKBoldrino · Accepted solution]

I solved the problem by using a second scenario table for the second scenario slicer. 🙂

[LKBoldrino]

Stichtag	BucketDatum	Szenario	Aussenzinsen	Blz
30.09.2024	30.09.2024	Ist	5,8	1220
30.09.2024	31.10.2024	co1	4	1220
30.09.2024	31.10.2024	co 2	0.1	1220
30.09.2024	31.10.2024	co forward	1	1220
30.09.2024	30.11.2024.	co1	4	1220
30.09.2024	30.11.2024.	co2	5	1220

Each BLZ can have multiple scenarios. Depending on which BLZ is selected, the external interest rates for Bucket Date > Stichtag must be calculated according to two arbitrary scenarios selected in the slicer. The delta between the scenarios should also be displayed. This means that the output matrix will look as follows:

Column header: Bucket Date Columns: Scenario 1 / Scenario 2 / Delta

[Anonymous]

Hi, @LKBoldrino 

Can you provide example data or sample files? And what you expect the output to be. There is sensitive data that can be removed in advance. How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Best Regards,

Community Support Team _Charlotte

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