How to handle measure using DATESINPERIOD?

[ovonel]

I have my measure that works fine:

Hours LTM:=

CALCULATE(

[Registered Hours] ,

'Billable Type'[Billable Type]="Chargeable"

,DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-12,MONTH)

)

I use it on several visuals; to show the ‘last 12 months hours’ breaking them down by different dimensions…

It works!

Now I am trying to break them down by month:

But the results don't make sense…

I just realize it is just showing the last 12 months for January, and the last 12 months for December, and so on… whereas what I want is the last 12 months per month… that is, to put the total 1390,60 in the corresponding months…

How can I do it?

(I tried using just     [Registered Hours] ,'Billable Type'[Billable Type]="Chargeable"     but I get data aaall the way back to many years ago…)

[amitchandak · Accepted solution]

@ovonel , That will be rolling 12 for a month in selection.

you need to use an independent table in slicer and then you can have measure like

//Date1 is independent Date table, Date is joined with Table
new measure =
var _max = maxx(allselected(Date1),Date1[Date])
var _min = eomonth(_max, -12) +1
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))

Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI