Forum Discussion
MTD measure
Hi guys,
my MTD & YTD measure show the figures up to t-1 (last calender day) even though there are transactions done today, they won't be included.
This is my measure: MTD Deposits = CALCULATE(SUM(meAccountTransaction[Deposits]), DATESMTD(meCalendar[DateValue]))
Is there a trick to have today's transactions included?
Anonymous Many thanks mate.
Still for some reason, what you have suggested did give me blank figures when I filter "current month" however you gave me the idea of doing the following:
I created a duplicate column for (meAccountTransaction[TransactionDate]) changed the format to date only and then created a direct relationship between (meAccountTransaction[TransactionDate]) and (meCalendar[DateValue])
Appreciate your help, it was a spot on. am getting the figures up to the minute!
16 Replies
DATESMTD will go to the last date in the current filter context. There is probably something about the way you're filtering your page or your visual that's eliminating today.
I made a quick test case. A table with entries on 9/1/2016, 9/28/2016 (yesterday), 9/29/2016 and 9/30/2016. Each date has an amount of 1. My test measure was similar to yours:
Amount MTD = CALCULATE(SUM(TestTable[Amount]), DATESMTD(DateTable[Date]))
I plotted it against the Month column from my date table, with no other filters. It gives a total of 4, which means it includes not only today, but also tomorrow. If it had incorrectly stopped at t-1 like yours it would have totaled to 2. If I filter the visual so it only includes dates today and earlier, it totals correctly to 3. So the problem isn't in your formula, which means it should be either some report/page/visual filter or slicer, or it's missing data (i.e. your date table doesn't include today's date, or you forgot that you haven't actually refreshed your dataset this morning).
Anonymous thanks!
You are right, I am using a filter on the page which is "Is current month" taking it from my Calander: Is Current Month = IF(FORMAT(meCalendar[DateValue],"YYYY-MM")=FORMAT(CALCULATE(MAX(meAccountTransaction[TransactionDate]),ALL(meAccountTransaction)),"YYYY-MM"),"Yes","No")
The reason am using this filter is to have all MTD measures working on this report, otherwise the measures will show blank like below.
Could be my "Is current Month" need some adjustment?
majdkaid22 yes, your Is Current Month formula is the source of the problem. I'm sure you've already seen my suggested revision for that formula. Just be aware that if somehow transaction data gets entered with tomorrow's date, your MTD measure will include those too.
Is your date table is updated with the today's date as well?
BhaveshPatel I have dates up the end of 2018 in my calander
and the "Is current Month" I use is: Is Current Month = IF(FORMAT(meCalendar[DateValue],"YYYY-MM")=FORMAT(CALCULATE(MAX(meAccountTransaction[TransactionDate]),ALL(meAccountTransaction)),"YYYY-MM"),"Yes","No")
Could be something to do with the above "Is current Month"?
majdkaid22 try this instead
Is Current Month = IF( MONTH(meAccountTransaction[TransactionDate]) = MONTH(TODAY()) && YEAR(meAccountTransaction[TransactionDate]) = YEAR(TODAY()), "Yes", "No" )
