Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
// 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
D
@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.
// 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
D
Hi @Selded ,
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
I tried the above measure and my visualizations couldnt display. I replaced table name with my facts table name as indicated.
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
D
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |