Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin 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.
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…)
Solved! Go to Solution.
@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
@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
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
14 | |
11 | |
10 | |
10 | |
9 |
User | Count |
---|---|
18 | |
13 | |
12 | |
11 | |
8 |