Forum Discussion
Helper IIHow to get Slicer Information for DAX Calculation
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
Super UserHi AlvinLy
Your measure is correct and is doing what is expected. The problem lies with Asia and Africa not being in June 01-2024 so while total previous expense for the date is correct, it cannot make a comparison for those two locations since those rows do not exist in the said d ate. Your workaround is to make the relationship inactive and use USERELATIONSHIP in the measure (but you'll need to do this for all measures that use location), create another loaction table to be used solely when there is a need to compare by location with the previous period (still using USERELATIONSHIP) or make those two locations exist in your fact table for those dates. You can see in the table that since the relationship is inactive, current expense is showing the total for each period instead of by location for each perio.d