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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.