Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Filters & DAX

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 

General Comment
Need Help
Tips and Tricks
Tutorial Requests
  • selimovd's avatar
    selimovd
    5 years ago

    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
     

3 Replies

  • selimovd's avatar
    selimovd
    Most Valuable Professional
    5 years ago

    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
     
    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Hi Anonymous 

         

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