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...
Need Help
  • AlvinLy's avatar
    AlvinLy
    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

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

Hi Danextian,

 

Thanks for your quick response to this. Since this is fabricated data, i'm not sure if it's currently viable to disconnect the relationship and change all measures in my actual dataset. Additionally, knowing the current expense is vital in the current calculation. Do you know of any other work arounds? I've been trying to find some robust workarounds that don't rely on specific duration between time periods.

I do agree the main problem here is how do we get the current report month (as chosen by the slicer) in our calculation for every single location. If we can solve that and get the variable working then all other calculations are simple

 

Thanks

danextian's avatar
danextian
Icon for Super User rankSuper User
2 years ago

Hi AlvinLy 

 

After fiddling more with your data, I've realized that your previous date calculation is using the report date from the expense table instead of from the report table. Africa and Asia don't have a record for  June 1 so naturally the previous date for these are blank (they have blank previous date and there is no date earlier than that). Also, use the date and location columns from the from the dimension tables instead of from expense.

 

 

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

    Hi Danextian,

    Yes i am aware that it is missing for the previous period. Which is the main question of my post, if we have period to period data, but say one expense, like travel, was present in last period but not in the current period, how do we get that information since the expense is tied to the current period date which is used to calculate the previous period date. Also, to make the calculation robust, the time between period is not exact.

    Again this is fabricated data, but the first scenario definitely happens in my actual dataset. The robustness built into the question is the other challenge that I am facing with to make this a DAX coding issue rather than just a "clean your data properly" issue. 

     

    Thanks for looking at the data!