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
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.
Helper IIHi 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!