topic Re: MTD measure and Date Filters in DAX Commands and Tips

MTD measure and Date Filters

Anonymous — Mon, 29 Jun 2020 14:08:32 GMT

All,

Please i have a MTD measure that works fine. I have included this measure in a table visualization. i have a date slicer on my report page.

I want the MTD measure figures to remain unchanged if i apply the date filter.

example if my date slicer is for 1st - 18th June, MTD should display values for June 1st to June 28th.

Can this be done, or i am missing something.

Below are my snips.

MTD measure =

MTD2 = Calculate([MeasureName],DATESMTD(CalendarTable[Date]))

Thanks for your hepl. really appreciate.

Re: MTD measure and Date Filters

Pragati11 — Mon, 29 Jun 2020 14:18:44 GMT

Hi Anonymous ,

Try to modify your measure as follows:

MTD2 = Calculate([MeasureName], FILTER(ALL('YOUR TABLENAME'), DATESMTD(CalendarTable[Date])))

Replace 'YOUR TABLENAME' in above dax with your table-name.

Thanks,

Pragati

Re: MTD measure and Date Filters

Anonymous — Mon, 29 Jun 2020 14:26:32 GMT

Hi Pragati11

Would you please explain to me how is this code taken from your formula

FILTER(ALL('YOUR TABLENAME'), DATESMTD(CalendarTable[Date]))

going to work?

As far as the documentation for FILTER goes, the second argument must be a BOOLEAN expression that's calculated for each of the rows of the first argument. The second argument, though, is a table in your FILTER, not a bool expression.

Thanks.

Best

Re: MTD measure and Date Filters

Anonymous — Mon, 29 Jun 2020 14:31:08 GMT

Anonymous,

This problem does not have a clear answer because the description is not clear. The fact is that if you change the upper bound of your date range, the measure will always change because it's tied to the calendar. Of course, upper bounds used by a measure can be any functions of the current selection of the dates in the calendar but you have not stated what the upper bound for the measure should really be when you change your selections in the slicer.

Best
D

Re: MTD measure and Date Filters

Anonymous — Mon, 29 Jun 2020 14:54:17 GMT

Pragati11 ,

I tried the above measure and my visualizations couldnt display. I replaced table name with my facts table name as indicated.

Re: MTD measure and Date Filters

Anonymous — Mon, 29 Jun 2020 15:11:37 GMT

Anonymous

Thanks for the reply, i have maintained my upper bound at today -1 with the lower bound filtering to any data within the current month.

With this, the MTD is working fine, but the moment i change the lower bound, MTD changes.

Re: MTD measure and Date Filters

Anonymous — Mon, 29 Jun 2020 15:26:34 GMT

// If you keep the upper bound in your // selection of dates constant, it does // not matter what lower bound you select, // the measure MTD2 will always be the same. // This is due to how DATESMTD works. If, // however, the measure changes, it means // you're not doing something correctly. // For instance, CalendarTable should be marked // as the Date table in the model. Please check // dax.guide/datesmtd to understand how the // function works. Since the documentation states // that the function is equivalent to this: // //DATESBETWEEN ( // <Dates>, // STARTOFMONTH ( LASTDATE ( <Dates> ) ), // LASTDATE ( <Dates> ) //) // // it's easy to see that in the calculation // only the last date, upper bound, plays a role // (see the dependence only on LASTDATE). // In a word, the measure below should work // correctly in the sense that its value does not // depend on the lower bound of the selection made // in CalendarTable[Date]. MTD2 = CALCULATE( [MeasureName], DATESMTD( CalendarTable[Date] ) )

Best