Forum Discussion

sam_hoccane's avatar
sam_hoccane
Helper I
4 years ago
Solved

Multiplication with Previous Values

Excel File: Link   Hello, Could you please help me write the following DAX Querry? I want to create a measure to calculate values highlighted blue in the predicted-CO2 emission column using the...
  • tamerj1's avatar
    tamerj1
    4 years ago

    sam_hoccane 
    Your wishes are my command ğŸ˜‰ Here is the workable solution exactly as you wish 
    https://we.tl/t-bo914kjbfJ

    Predicted_Measure_4 = 
VAR CurrentEmission = SUM ( Data[Emission] )
VAR CurrentMonth = SELECTEDVALUE ( data[Year_Month] )
VAR CurrentForcast = SELECTEDVALUE ( Data[forcast] )
VAR PreviousMonths = FILTER ( ALLSELECTED ( Data ), data[Year_Month] < CurrentMonth )
VAR PreviousN_Months = FILTER ( PreviousMonths, Data[forcast] = "N" )
VAR PreviousN_Month = MAXX ( PreviousN_Months, data[Year_Month] )
VAR PreviousN_Emission = SUMX ( FILTER ( PreviousN_Months, data[Year_Month] = PreviousN_Month ), Data[Emission] )
VAR YearMonthOnly = DISTINCT ( SELECTCOLUMNS ( PreviousMonths, "@YearMonth", Data[Year_Month] ) )
VAR NumberOfY_Months = COUNTROWS ( FILTER ( YearMonthOnly, [@YearMonth] > PreviousN_Month && [@YearMonth] <= CurrentMonth ) ) + 1
VAR Result =
    IF (
        CurrentForcast = "N",
        CurrentEmission,
        PreviousN_Emission * POWER ( 1.15, NumberOfY_Months )
    )
RETURN
    Result
sam_hoccane's avatar
sam_hoccane
Helper I
4 years ago

tamerj1 

 

Please use this link for sample data - DATA

There was some issue  in early sample. 

 

I would really appreciate for any given help. Thankuyou 🙂

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    Hi sam_hoccane 
    Do you want that for each type / each manager?

    • sam_hoccane's avatar
      sam_hoccane
      Helper I
      4 years ago

      Yes please, I also want  apply filter on Manager level and also I need without manager filter. 

       

      • tamerj1's avatar
        tamerj1
        Community Champion
        4 years ago

        sam_hoccane 
        Your wishes are my command ğŸ˜‰ Here is the workable solution exactly as you wish 
        https://we.tl/t-bo914kjbfJ

        Predicted_Measure_4 = 
VAR CurrentEmission = SUM ( Data[Emission] )
VAR CurrentMonth = SELECTEDVALUE ( data[Year_Month] )
VAR CurrentForcast = SELECTEDVALUE ( Data[forcast] )
VAR PreviousMonths = FILTER ( ALLSELECTED ( Data ), data[Year_Month] < CurrentMonth )
VAR PreviousN_Months = FILTER ( PreviousMonths, Data[forcast] = "N" )
VAR PreviousN_Month = MAXX ( PreviousN_Months, data[Year_Month] )
VAR PreviousN_Emission = SUMX ( FILTER ( PreviousN_Months, data[Year_Month] = PreviousN_Month ), Data[Emission] )
VAR YearMonthOnly = DISTINCT ( SELECTCOLUMNS ( PreviousMonths, "@YearMonth", Data[Year_Month] ) )
VAR NumberOfY_Months = COUNTROWS ( FILTER ( YearMonthOnly, [@YearMonth] > PreviousN_Month && [@YearMonth] <= CurrentMonth ) ) + 1
VAR Result =
    IF (
        CurrentForcast = "N",
        CurrentEmission,
        PreviousN_Emission * POWER ( 1.15, NumberOfY_Months )
    )
RETURN
    Result