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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

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
July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

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