Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sam_hoccane
Helper I
Helper I

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 formula given in the formula column.


So, for the forecast emission for July, I am taking the previous month's value ( i.e. actual emission in june=100) and multiplying it by 1.15 to get a 15% increase for July. For August, I take the forecast value of July (i.e. 100*1.15= 115) and multiply it by 1.15 to get a 15% increase ( (i.e. 115*1.15= 132). 

sam_hoccane_2-1657573075912.png

Any given help will be appreciated. 

 

Thanks,

Sam

 

 

 

 

1 ACCEPTED SOLUTION

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

1.png2.png

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

View solution in original post

12 REPLIES 12
sam_hoccane
Helper I
Helper I

@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 🙂

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

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

 

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

1.png2.png

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

@tamerj1  thankyou so much for your help 🙂  your are my angel  🙂 

 

I have one last request. I was trying to find  cummulative emission but it was not producing the results.  Would you please suggest way arround. 

 

Predicted _Cumulative_Emission =
CALCULATE( [Predicted_measure_4] , FILTER ( ALLSELECTED(data) ,data[Date] < max (data[Date])))
 
Here is link to data

 

sam_hoccane_0-1657746258486.png

 

 

@sam_hoccane 
Here you go https://we.tl/t-tfEd37slkZ

1.png

Comulative Predected = 
VAR CurrentMonth = MAX ( data[Year_Month] )
VAR T1 = FILTER ( ALLSELECTED ( data[Year_Month] ), data[Year_Month] <= CurrentMonth )
RETURN
    CALCULATE ( SUMX ( T1, [Predicted_Measure_4] ), ALLEXCEPT ( data, data[Type], data[Manager] ) )
Dhacd
Resolver III
Resolver III

Hi @sam_hoccane,

Please find another way to achieve the desired solution below,

 

PredictedEmissions = 

Var TableN = filter('Table','Table'[forecast]="N")
Var MaxMonth = CALCULATE(MAX('Table'[Month]),TableN)
Var LastEmission = CALCULATE(MAX('Table'[CO2 Emission]),'Table'[Month]=MaxMonth,all('Table'))
Var Difference =datediff(MaxMonth,'Table'[Month],MONTH)
Var Predicted = (LastEmission *power(1.15,Difference))
return  if( 'Table'[forecast]="Y",Predicted,'Table'[CO2 Emission])

 


Screenshot Attached

Dhacd_0-1657610024901.png

 



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

Regards,

Atma.

@Dhacd 
Good job!
A few issues here.
We don't know whether "N" and "Y" cycle overtime. Therefore the maximum Month with "N" might be after the first "Y" row.
If a month reading is missing from the data (not taken or not recorded for any reason) the DATEDIFF will not provide a correct result. COUNTROWS seems to be more reliable in this case.

tamerj1
Super User
Super User

Hi @sam_hoccane 
Here is a sample file with the solution https://we.tl/t-cRyZP69iRO

For calculated column:

1.png

 

 

Predicted = 
VAR CurrentEmission = Sheet1[CO2 Emission]
VAR CurrentMonth = Sheet1[Month]
VAR PreviousMonths = FILTER ( Sheet1, Sheet1[Month] < CurrentMonth )
VAR PreviousN_Months = FILTER ( PreviousMonths, Sheet1[forecast] = "N" )
VAR PreviousN_Month = MAXX ( PreviousN_Months, Sheet1[Month] )
VAR PreviousN_Emission = MAXX ( FILTER ( PreviousN_Months, Sheet1[Month] = PreviousN_Month ), Sheet1[CO2 Emission] )
VAR NumberOfY_Months = COUNTROWS ( FILTER ( PreviousMonths, Sheet1[Month] > PreviousN_Month && Sheet1[Month] <= CurrentMonth ) ) + 1
VAR Result =
    IF (
        Sheet1[forecast] = "N",
        Sheet1[CO2 Emission],
        PreviousN_Emission * POWER ( 1.15, NumberOfY_Months )
    )
RETURN
    Result

 

For measure:

1.png

 

Predicted Measure = 
VAR CurrentEmission = SELECTEDVALUE ( Sheet1[CO2 Emission] )
VAR CurrentMonth = SELECTEDVALUE ( Sheet1[Month] )
VAR CurrentForcast = SELECTEDVALUE ( Sheet1[forecast] )
VAR PreviousMonths = FILTER ( ALL ( Sheet1 ), Sheet1[Month] < CurrentMonth )
VAR PreviousN_Months = FILTER ( PreviousMonths, Sheet1[forecast] = "N" )
VAR PreviousN_Month = MAXX ( PreviousN_Months, Sheet1[Month] )
VAR PreviousN_Emission = MAXX ( FILTER ( PreviousN_Months, Sheet1[Month] = PreviousN_Month ), Sheet1[CO2 Emission] )
VAR NumberOfY_Months = COUNTROWS ( FILTER ( PreviousMonths, Sheet1[Month] > PreviousN_Month && Sheet1[Month] <= CurrentMonth ) ) + 1
VAR Result =
    IF (
        CurrentForcast = "N",
        CurrentEmission,
        PreviousN_Emission * POWER ( 1.15, NumberOfY_Months )
    )
RETURN
    Result

 

 

Hello @tamerj1 @Dhacd ,

 

Thankyou so much for the help. 

Would it be possible to provide above querry into measure. 

 

 

@sam_hoccane 
Please check the updated solution in the original reply

Hello @tamerj1 

 

I tried with given measure on summarized data and, it is working fine . However, when I am try on bigger dataset ( here is example - data ) it is not producing  any results. Would it possible if you take a peek of code and suggest any solutions.

 

Thankyou so much for helping me 🙂 

 

sam_hoccane_0-1657721807440.png

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors