Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
Anonymous
Not applicable

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…

ovonel_0-1672905209089.png

 

It works!

 

 

 

Now I am trying to break them down by month:

ovonel_1-1672905365409.png

 

 

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
amitchandak
Super User
Super User

@Anonymous , 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

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

View solution in original post

1 REPLY 1
amitchandak
Super User
Super User

@Anonymous , 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

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

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