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

filter visually but not the underlying calculation?

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? 

 

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Not as eloquent of a solution as i had hoped but using zoom sliders achieved what i was after. 

View solution in original post

3 REPLIES 3
amitchandak
Super User
Super User

@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

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

Not as eloquent of a solution as i had hoped but using zoom sliders achieved what i was after. 

Anonymous
Not applicable

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

 

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.