How to get Slicer Information for DAX Calculation

[AlvinLy]

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 link to the data before continue reading, specifically "Previous Period Problem" tab. Note all the data was fabricated but I made it similar to my current situation. https://drive.google.com/file/d/1ITIrPYp-MI85XORY_XIb7D_s2gCGT7Tv/view?usp=drivesdk

After looking at the information if you run through the three possible reporting dates, on June-01-24, we are missing data for Africa and Asian rows under last period expense. I tried selectedvalue with other manipulations and filter removals but can't seem to get this to work. 

I appreciate the advice!

[AlvinLy · Accepted solution]

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]

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]

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]

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!

[danextian]

Hi @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