Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateJoin 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.
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).
Any given help will be appreciated.
Thanks,
Sam
Solved! Go to Solution.
@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
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
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.
@sam_hoccane
Here you go https://we.tl/t-tfEd37slkZ
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] ) )
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
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.
Hi @sam_hoccane
Here is a sample file with the solution https://we.tl/t-cRyZP69iRO
For calculated column:
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:
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
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
15 | |
9 | |
7 | |
7 | |
6 |
User | Count |
---|---|
22 | |
11 | |
10 | |
10 | |
8 |