Dax - DATESMTD

[DaveBlakeMAAT]

Hi

I have a model of transactional data, which does include future dates, and i am having issues with DATESMTD, in the fact it appears to be picking the future dated transactions as the basis of calculating the MTD values

I have the following base measure (works fine):

Net Expenditure Total (£):=SUMX (
FILTER ( Payables, Payables[TYPE] IN { "PI", "PC" } ),
Payables[NET_AMOUNT]
)

My DATESMTD measures that i have tried are as follows:

(this returns the future months value)
MTD Expenditure (£)2:=CALCULATE (
[Net Expenditure Total (£)],
DATESMTD ( 'Calendar'[Date])
)

Therefore, my next thought was to use Filter to filter the calendar table to eliminate future transactions and this returns Blank
MTD Expenditure (£):=CALCULATE (
[Net Expenditure Total (£)],FILTER('Calendar','Calendar'[Date]<=EOMONTH(TODAY(),0)),
DATESMTD ( 'Calendar'[Date])
)

Any help is greatly appreciated.

Regards

Dave

 

[Matt Allington]

I call this calendar over run. The way I solve it is to create a calculated column that sets every date as “past” or “future”, or something like that. Eg IF(cal[date]>today(),”Future”,”Past”)

you can the. Simple filter on “past”

 

[DaveBlakeMAAT]

Thanks Matt

My apologies for the delayed response, that is a very helpful tip to remember for the future.

Regards

Dave