Date Filtered Calculation Not Working As Expected

[JB]

I have three measures on a report that calculate stock value and the change in stock value on a monthly basis. They need to be receptive to two slicers; a date slicer and a Component Type slicer. They work by first establishing a variable date range and then using an IF statement, so that if a month is selected with the slicer they show

1) the selected month's value,

2) the preceding month's value,

3) the difference between them.

If the date slicer is left blank, it shows the values for the two most recent months in the dataset.

Here are the measure expressions for the two monthly values:

Selected month's value = 
var _selectmonthbegin = eomonth(SELECTEDVALUE('Component Type Nov - Jan'[Date]),-1)+1       // begin of sliced month
var _selectmonthend = eomonth(SELECTEDVALUE('Component Type Nov - Jan'[Date]),0)            // end of sliced month
var _currentmonthstart = eomonth(MAX('Component Type Nov - Jan'[Date]),-1)+1                // begin of most recent month
var _currentmonthend = eomonth(MAX('Component Type Nov - Jan'[Date]),0)                     // end of most recent month

return
IF( not ISBLANK('Component Type Nov - Jan'[4. Selected month]),
CALCULATE(
    SUM('Component Type Nov - Jan'[Total Stock Replacement cost]), FILTER(('Component Type Nov - Jan'),'Component Type Nov - Jan'[Date]  >= _selectmonthbegin && 'Component Type Nov - Jan'[Date] <= _selectmonthend))
    ,
    CALCULATE(
    SUM('Component Type Nov - Jan'[Total Stock Replacement cost]), FILTER(('Component Type Nov - Jan'),'Component Type Nov - Jan'[Date]  >= _currentmonthstart && 'Component Type Nov - Jan'[Date] <= _currentmonthend)))

Month before selected value = 
var _lastmonthbegin = eomonth(SELECTEDVALUE('Component Type Nov - Jan'[Date]),-2)+1         // begin of month before
var _lastmonthend = eomonth(SELECTEDVALUE('Component Type Nov - Jan'[Date]),-1)             // end of month before
var _onebeforestart = eomonth(MAX('Component Type Nov - Jan'[Date]),-2)+1                   // begin of one before most recent month
var _onebeforeend = eomonth(MAX('Component Type Nov - Jan'[Date]),-1)                       // end of one before most recent month

return
IF( not ISBLANK('Component Type Nov - Jan'[4. Selected month]),
CALCULATE(
    SUM('Component Type Nov - Jan'[Total Stock Replacement cost]), FILTER(('Component Type Nov - Jan'),'Component Type Nov - Jan'[Date]  >= _lastmonthbegin && 'Component Type Nov - Jan'[Date] <= _lastmonthend))
    ,
    CALCULATE(
    SUM('Component Type Nov - Jan'[Total Stock Replacement cost]), FILTER(('Component Type Nov - Jan'),'Component Type Nov - Jan'[Date]  >= _onebeforestart && 'Component Type Nov - Jan'[Date] <= _onebeforeend)))

What's confusing me is that despite having identical structure (I copied and pasted the code and just swapped the time parameters), the Month Before Selected measure does not behave the same way as the Selected Month measure. Selected Month works perfectly whether a month has been sliced or not, but Month Before Selected only works if no month is sliced - once you slice a month it returns (Blank).

My immediate thought was that the variable date range wasn't working, but I have put them in their own measures and used callout cards to confirm that they do work properly. 

Adding ALL to the measure so that it's

CALCULATE(
    SUM('Component Type Nov - Jan'[Total Stock Replacement cost]), FILTER(ALL('Component Type Nov - Jan'),'Component Type Nov - Jan'[Date]  >= _lastmonthbegin && 'Component Type Nov - Jan'[Date] <= _lastmonthend))

stops it returning (Blank) and allows it to interact with the date slicer, but it does not respond to the Component Type slicer and returns a grand total of the value of all component types.

If anyone could tell me why the Month Before Selected doesn't work in the same way as Selected Month despite having identical code, and how to fix this issue I would be very appreciative.

Thanks.

[JB]

Taking into account how over engineered my original DAX formulas were, I've made a much simpler version but it still returns (Blank). 

Simplified = 
SUMX(FILTER('Component Type Nov - Jan', 'Component Type Nov - Jan'[Date] = SELECTEDVALUE('Component Type Nov - Jan'[Date])-1), 'Component Type Nov - Jan'[total stock sum])

Removing the -1 returns the correct value but including -1 breaks it, even though SELECTEDVALUE('Component Type Nov - Jan'[Date])-1) returns the correct month when used on its own in a call out card. 

No idea why.

[Whitewater100]

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.

[JB]

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?

[Whitewater100]

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.

[JB]

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.