cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Post Prodigy

## How to handle measure using DATESINPERIOD?

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…)

1 ACCEPTED SOLUTION
Super User

@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

Super User

@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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors