Filters & DAX

[Anonymous]

Hello dear community, how are you?

I have this case:

I need 3 DAX measurements that calculate the following for me:

1. I want to calculate the Total Forecast3 2021.

2. I want to calculate the Total Budget 2022.

3. And another formula that calculates the difference between Forecast3 2021 vs Budget 2022 (Budget 2022 - Forecast3 2021).

The table contains these 3 columns that are necessary for the calculation:

1. Report Date "Fecha Reporte": This covers all the months of 2020, 2021, 2022.
2.  USD Amount "Monto USD": This is the column that contains the value of the amount.
3. Opex type "Tipo Opex": This contains the budget classifications or segmentations (Actual, Budget, Forecast1, Forecast2, Forecast3).

Table

Also bear in mind that I already have a Total measure for each Budget and Forecasts segment.

I even leave the PBIX to you so that you can help me directly with it. I am very urgent and I will be eternally grateful to whoever helps me. Download pbix

@Greg_Deckler @CNENFRNL @Anonymous @amitchandak 

[selimovd · Accepted solution]

Hey @Anonymous ,

the following measures should do that.

For your case 1:

Forecast3 2021 = 
CALCULATE(
    SUM( 'Fact_ Budget Management'[Monto USD] ),
    'Fact_ Budget Management'[Tipo Opex] = "Forecast3",
    YEAR( 'Fact_ Budget Management'[Fecha Reporte] ) = 2021
)

Case 2:

Total Budget 2022 = 
CALCULATE(
    SUM( 'Fact_ Budget Management'[Monto USD] ),
    'Fact_ Budget Management'[Tipo Opex] = "Budget",
    YEAR( 'Fact_ Budget Management'[Fecha Reporte] ) = 2022
)

And case 3:

Difference Forecast3 2021 vs Budget 2022 = [Forecast3 2021] - [Total Budget 2022]

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Anonymous]

@selimovd Thank you very much, it is exactly what I needed.

[Anonymous]

Hi @Anonymous 

By hard-coding the names of the years, you're shooting yourself in the foot. You'll suffer later.