LTM Values with status filter

[Anonymous]

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.

[amitchandak · Accepted 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.