Forum Discussion
Date Filtered Calculation Not Working As Expected
Hi:
I'm wondering if you would rather add a running month index to your data table and filter months this way
Running Month Index =
VAR minyear = YEAR(MIN(Dates[Date]))
VAR thisyear = YEAR(Dates[Date])
Return
(thisyear - minyear) * 12 + MONTH(Dates[Date])
Then you can create your measure and filter to MAX(Month Index) or MAX[Month Index]-1. It's much easier to read.
For your inventory positions the Functions OPENINGBALANCEMONTH or CLOSINGBALANCEMONTH are nice.
Used like this:
Clsoing Inventory = CLOSINGBALANCEMONTH ( [Inventory Value], Dates[Date])
This could really reduce your code. Just a thought.
That probably is the more sensible option, I went off what I already knew which is why it's maybe not the most intuitive coding. I'll bear your advice in mind for the future though, thanks.
Do you have any ideas why the expressions above are behaving differently despite being practically identical?
Maybe you can change your slicer to Year-Month, in the event you are only using month and you have multiple years?
I generally use the simple index methode it's easier to follow and each month only has oe value during a calculation.
DAX Studio is a good place to try out the scenarios though.
My slicer is for Month-Year, and I only have three months worth of data in there so far as it's a new dashboard to be rolled out this year. Besides, the other measures use the same time intelligence logic and work fine.
