Forum Discussion
How to get Slicer Information for DAX Calculation
- 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
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