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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
thisguy
Advocate I
Advocate I

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

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

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

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors