Time Intelligence working but not aggregating on totals

[ChrisN32]

Hi all,

I'm implementing Time Intelligence, but I need the Time Intelligence to be more explicit. I.e. Current MTD should only show values for the current month (not MTD measures for the previous months as well if no date is selected).

Default Time Intelligence MTD expression:

CALCULATE (
     SELECTEDMEASURE (),
     DATESMTD ( ‘Date'[Date] )
)  

Adapted TI measures to exclude these extra dates:

CALCULATE ( SELECTEDMEASURE (), DATESMTD ( 'Call Calendar'[Date] ), FILTER ( 'Call Calendar', 'Call Calendar'[Date] >= DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 01 ) ), FILTER ( 'Call Calendar', 'Call Calendar'[Date] < TODAY () ), ALL ( 'Call Calendar' ) )

While this seems to have the intended affect, as per below screenshot, it is not showing any totals/aggregations.

Any advice on where I've gone wrong or what's happening?

Cheers

Chris

[ChrisN32 · Accepted solution]

I removed the filter on all dates, this seems to have fixed the issue and is behaving more like I expect MTD.

Code if anyone is interested:

 CALCULATE ( SELECTEDMEASURE (), FILTER ( 'Call Calendar', 'Call Calendar'[Date] >= EOMONTH ( TODAY (), -1 ) + 1 && 'Call Calendar'[Date] <= TODAY () ) )

Thanks Amit!

[Anonymous]

@ChrisN32 

Of course it's ignoring... because you should never put a whole table as a filter into CALCULATE. Only the columns you really need. The golden rule of DAX: You should never filter a table if you can filter a column. This is where your problem is.

[ChrisN32]

Hey Daxer

Ahhhh, thanks. Are you referring to this specifically? 

FILTER ( ALL ( 'Call Calendar' )...)

[Anonymous · Accepted solution]

I'm talking about this:

FILTER ( 'Call Calendar', 'Call Calendar'[Date] >= DATE....

What this effectively does is it prevents the directive ALL( 'Call Callendar' ) from taking effect because you're overwriting context with what's in the context already. Such things should never be attempted in DAX code.

[ChrisN32]

Hi Amit,

Thanks for the quick reply! That got my much closer, the total MTD is displaying and looks correct.

Unfortunately it's still showing values for other months, in fact it's showing the current MTD against all the other months.

[amitchandak]

@ChrisN32 , what you have taken in isfiltered, it should be the table from where the month is coming

or try like

MTD =
if( isfiltered('Date') || isinscope('Date'[Month]) || isinscope('Date'[Month year]), // change column name as per need
CALCULATE (
SELECTEDMEASURE (),
DATESMTD ( 'Date'[Date] )
)
,
CALCULATE(SELECTEDMEASURE (), FILTER(ALL('Date'),'Date'[Date] >= eomonth(today(),-1)+1 && 'Date'[Date] <= today() ) )
)

[ChrisN32]

Thanks for the help, I'm learning so much! It's ignoring the month completely now

[ChrisN32 · Accepted solution]

I removed the filter on all dates, this seems to have fixed the issue and is behaving more like I expect MTD.

Code if anyone is interested:

 CALCULATE ( SELECTEDMEASURE (), FILTER ( 'Call Calendar', 'Call Calendar'[Date] >= EOMONTH ( TODAY (), -1 ) + 1 && 'Call Calendar'[Date] <= TODAY () ) )

Thanks Amit!

[amitchandak]

@ChrisN32 , Handle filter

MTD =
if( isfiltered('Date'),
CALCULATE (
SELECTEDMEASURE (),
DATESMTD ( 'Date'[Date] )
)
,
CALCULATE(SELECTEDMEASURE (), FILTER(ALL('Date'),'Date'[Date] >= eomonth(today(),-1)+1 && 'Date'[Date] <= today() ) )
)

Last MTD

if( isfiltered('Date'),
CALCULATE (
SELECTEDMEASURE (),
DATESMTD ( dateadd('Date'[Date],-1, month) )
)
,
CALCULATE(SELECTEDMEASURE (), FILTER(ALL('Date'),'Date'[Date] >= eomonth(today(),-2)+1 && 'Date'[Date] <= date(year(today()),month(today()) -1,day(today())) ) )