Forum Discussion

AlvinLy's avatar
AlvinLy
Icon for Helper II rankHelper II
2 years ago
Solved

How to get Slicer Information for DAX Calculation

Hello Fabric Community, I have seen some posts and links with related information on getting slicer information for DAX calculation but I'm struggling to get it to work for my scenario. Please see...
  • AlvinLy's avatar
    2 years ago

    Hello,

     

    I think i found the method and formula to figure this out if anyone else is having a similar problem. 

    I use the FILTERS() function to find out that what the currently chosen date is on the slicer. I then calculate the previous period by calculating max date that is less than the currently chosen date but also removing all unnecessary filter with REMOVEFILTERS(), these would be everything except for the project name if that was chosen. I understand this is not as robust, since I need to remove filters, but i think for what I need it works for now and hopefully can help others with this. 

    See updated last period expense code:

     

     

    Last Period Expense = 
    VAR CurrentPeriod = FILTERS(Expenses_F[Report Date])
    VAR PreviousPeriod = CALCULATE(
        MAX(Expenses_F[Report Date]),
        REMOVEFILTERS(Location_D[Location]),
        Expenses_F[Report Date] < CurrentPeriod
    )
    VAR Expense = CALCULATE(
        SUM(Expenses_F[Amount]),
        Expenses_F[Report Date] = PreviousPeriod
    )
    RETURN
    Expense

     
    Thanks for everyone commenting and viewing this post