topic Re: filter visually but not the underlying calculation? in DAX Commands and Tips

filter visually but not the underlying calculation?

Anonymous — Mon, 06 Jun 2022 05:49:00 GMT

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?

Re: filter visually but not the underlying calculation?

amitchandak — Mon, 06 Jun 2022 09:22:47 GMT

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

Re: filter visually but not the underlying calculation?

Anonymous — Mon, 06 Jun 2022 23:42:21 GMT

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

Re: filter visually but not the underlying calculation?

Anonymous — Wed, 08 Jun 2022 03:23:15 GMT

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