Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I need to use DAX to calculate the LTM value in 2 steps as illustrated below in the excel.
Step-1: the monthly value is calculate by an average value of last two months.
AC15 = (AB12 + AC12) / 2
AB15 = (AA12 + AB12) / 2
...
Step-2: passing these average values as an input of each month into a sum of rolling 12 months. And then make an average value of the result from the sum of rolling 12 months.
AC18 = average(R15:AC15)
The actual table is not like this excel. There are one column [Date] and another column [ActivePortfolio].
I use this DAX formula for step-1. This gives me correct result.
[MonthlyValue] := CALCULATE (SUM([ActivePortfolio]), DATESINPERIOD (row5, LASTDATE( [Date] ), -2, MONTH)) / 2
But if I use the following DAX formula in step-2, it returns a very small value for that month (AC18).
[LTMValue] := CALCULATE ([MonthlyValue], DATESINPERIOD (row5, LASTDATE( [Date] ), -12, MONTH)) / 12
So what's wrong in the DAX [LTMValue]?
Solved! Go to Solution.
If [MonthlyValue] is calculated correctly, then [LTMValue] is THE AVERAGE of [MonthlyValue] over the period of 12 months, not just CALCULATE( [MonthlyValue], ...). You have to do two things:
By the way, you have to have a 'Date' table that joins to your Portfolios and which is marked as DATE TABLE in the model and covers full years of the dates found in Portfolios[Date].
var __lastVisibleDate = LASTDATE( 'Date'[Date] )
var __startDate = PREVIOUSYEAR( __lastVisibleDate ) + 1
var __months =
CALCULATETABLE(
VALUES( 'Date'[Month] ), -- Month must be unique across years
DATESBETWEEN(
'Date'[Date],
__startDate,
__lastVisibleDate
)
)
var __average =
CALCULATE(
AVERAGEX(
__months,
[MonthlyValue]
),
ALL( 'Date' )
)
return
__average
If the last visible date in your selections is the last date of a month, this will work correctly
If [MonthlyValue] is calculated correctly, then [LTMValue] is THE AVERAGE of [MonthlyValue] over the period of 12 months, not just CALCULATE( [MonthlyValue], ...). You have to do two things:
By the way, you have to have a 'Date' table that joins to your Portfolios and which is marked as DATE TABLE in the model and covers full years of the dates found in Portfolios[Date].
var __lastVisibleDate = LASTDATE( 'Date'[Date] )
var __startDate = PREVIOUSYEAR( __lastVisibleDate ) + 1
var __months =
CALCULATETABLE(
VALUES( 'Date'[Month] ), -- Month must be unique across years
DATESBETWEEN(
'Date'[Date],
__startDate,
__lastVisibleDate
)
)
var __average =
CALCULATE(
AVERAGEX(
__months,
[MonthlyValue]
),
ALL( 'Date' )
)
return
__average
If the last visible date in your selections is the last date of a month, this will work correctly
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
24 | |
20 | |
14 | |
12 |
User | Count |
---|---|
55 | |
48 | |
43 | |
19 | |
18 |