Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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? 

 

 

  • Anonymous's avatar
    Anonymous
    4 years ago

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

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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