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 a running total headcount that goes back many years. Total is: new starts - exits = current count. this calculation works and displays fine.
i want to display only the last 12 months totals - the problem is when i filter the visual to only show last 6 months it ignores all the starts and exits before that date - to fix this i changed the formula to include the ALL filter and it works. except i also need to be able to filter by department which i can no longer do thanks to the ALL filter.
is there a way to only display the last 12 months of a visual without impacting the calculation/still allowing a catagory filter?
Solved! Go to Solution.
Not as eloquent of a solution as i had hoped but using zoom sliders achieved what i was after.
@Anonymous , if there is no date filter. using a date table
use today if needed
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))
if you have a date selected and you want to show trend , then you need an independent date table
//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
Not as eloquent of a solution as i had hoped but using zoom sliders achieved what i was after.
Thanks @amitchandak ,
I am using a date table and i am getting the data i want if i display all 30 years of history. The problem is how to display only 6 months but keep the full total in a way i can still filter/slicer by department.
If I filter the visual to display only the last 6 months it only shows the total for people who started in the last six months. If i add an ALL clause it displays the correct total when filtered to show the last 6 months but i lose the ability to filter/slicer by department.
I had some help on another thread here is my measure:
Employee Count = VAR CurrentDate = MAX ( Dim_Calendar[Date] ) VAR SelectedData = ALLSELECTED ( FACT_Data ) VAR EmpData = FILTER ( SelectedData, FACT_Data[Start/Finish] = "Employment Date" ) VAR TerData = FILTER ( SelectedData, FACT_Data[Start/Finish] = "Termination Date" ) VAR Employed = COUNTROWS ( FILTER ( EmpData, FACT_Data[ Date ] <= CurrentDate ) ) VAR Terminated = COUNTROWS ( FILTER ( TerData, FACT_Data[ Date ] <= CurrentDate ) ) RETURN Employed - Terminated
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 | |
10 | |
10 | |
10 | |
9 |
User | Count |
---|---|
20 | |
13 | |
12 | |
11 | |
8 |