Dax - DATESMTD

DaveBlakeMAAT

Board Regular
Joined
Feb 28, 2016
Messages
186
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
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Matt Allington

MrExcel MVP
Joined
Dec 18, 2014
Messages
1,227
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

Board Regular
Joined
Feb 28, 2016
Messages
186
Thanks Matt

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

Regards

Dave
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,170
Messages
5,509,577
Members
408,744
Latest member
leen1234

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top