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 dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hello,
I created a measure to display the sum of the last twelve month margin values for each material in my table.
Now each material is given a status beforehand, depending on margin and revenue etc... in the current month, e.g. red,yellow,green. If I try and filter a visual containing LTM values by status, my LTM measure displays wrong values, since my status filter is applied to my entire data table and if the status ever changed in the LTM, for example 11month green, one month red and i filter by green, this one month wouldnt count to the LTM value.
My LTM Measure looks like this
Std. Margin LTM =
var CurrentDate = max(Scorecard_Date_Selector[Date])
var PreviousDate = EoMONTH(CurrentDate,-11)
Return
CALCULATE(
sum('VW_LEM_COMBINED_REGIONS'[STD_MARGIN]),
'VW_LEM_COMBINED_REGIONS'[Date] >= PreviousDate && 'VW_LEM_COMBINED_REGIONS'[Date] <= CurrentDate
)
I'm fairly new to Power BI and any help on how I would be able to filter by status while maintaining correct LTM values would be much appreciated.
Solved! Go to Solution.
@Anonymous , examples of rolling 12
Rolling 12 Sales =
var _max = maxx(allselcted(date),date[date]) // or today()
var _min = date(year(_max), month(_max)-12,1)
return
CALCULATE(SUM(Sales[Sales Amount]),filter(date, date[date] <=_max && date[date] >=_min))
or
Rolling 12 Sales =
var _max = maxx(allselcted(date),date[date]) // or today()
var _min = date(year(_max), month(_max)-12,1)
return
CALCULATE(SUM(Sales[Sales Amount]),filter(all(date), date[date] <=_max && date[date] >=_min))
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))
or
rolling 12 =
var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _min = date(Year(_max), month(_max) -12, Day(_max))+1,
BLANK())
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.
@Anonymous , examples of rolling 12
Rolling 12 Sales =
var _max = maxx(allselcted(date),date[date]) // or today()
var _min = date(year(_max), month(_max)-12,1)
return
CALCULATE(SUM(Sales[Sales Amount]),filter(date, date[date] <=_max && date[date] >=_min))
or
Rolling 12 Sales =
var _max = maxx(allselcted(date),date[date]) // or today()
var _min = date(year(_max), month(_max)-12,1)
return
CALCULATE(SUM(Sales[Sales Amount]),filter(all(date), date[date] <=_max && date[date] >=_min))
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))
or
rolling 12 =
var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _min = date(Year(_max), month(_max) -12, Day(_max))+1,
BLANK())
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.
User | Count |
---|---|
25 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
27 | |
13 | |
11 | |
9 | |
6 |