I've searched and searched to find a post with a similar issue, but I'm at a loss. Apologies if this has been answered before.
I'm currently facing an issue where my date table hierarchy will not properly filter rows in a slicer, especially when used with ALLSELECTED in a measure. I know the hierarchy is to blame (at least partly), because everything works as it should when I use the 'Date'[Date] column in the slicer as opposed to the hierarchy. My date table is set up with a relationship to the main fact table bi-directionally and with many-to-one cardinality. My auto time intelligence is turned off.
Here is the measure I'm trying to get working:
Monthly Total Cost Variance (%) =
VAR CurrentMonthTotal =
CALCULATE (
SUM ( Analytics[Total Cost ($)] ),
LASTDATE ( ALLSELECTED ( 'Date'[Date] ) ),
ALLEXCEPT ( Analytics, Analytics[Account Number] )
)
VAR PastMonthTotal =
CALCULATE (
SUM ( Analytics[Total Cost ($)] ),
DATEADD ( LASTDATE ( ALLSELECTED ( 'Date'[Date] ) ), -1, MONTH ),
ALLEXCEPT ( Analytics, Analytics[Account Number] )
)
RETURN
DIVIDE ( CurrentMonthTotal - PastMonthTotal, PastMonthTotal, BLANK () )
The desired behaviour is to have the measure display the variance for the most recent (filtered) month. If 'All' is selected in the slicer, it should display variances only for the most recent month, but with the hierarchy, it's will only display the most recent month, even if a month from the previous year is selected in the slicer. As I said, if I put the 'Date'[Date] column in the slicer instead, everything works as expected, so it has to be something with the way the hierarchy is constructed.
Any help is greatly appreciated!
