Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
majdkaid22
Helper V
Helper V

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? 

 

 

2 ACCEPTED SOLUTIONS

@KHorseman 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!

View solution in original post

The columns in the relationship must be the same. If one is datetime and the other is date only, there will be zero matches between the two columns.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

16 REPLIES 16
KHorseman
Community Champion
Community Champion

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).





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@KHorseman 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.

 

Capture.JPG

 

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.

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




BhaveshPatel
Community Champion
Community Champion

Is your date table is updated with the today's date as well?

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.

@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"
)

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@KHorseman it seems it has returned the correct (Yes/No) but when I apply it, all MTD measures are blank again. 

 

Could be because all my MTD measures are linked to the date in a calender? 

@majdkaid22 no, those formulas should all work as written. How are you applying Is Current Month? Are you using it in a visual filter or are you actually referring to it in another formula?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




no2.JPG

 

Here's a screenshot @KHorseman I applied it as a column, and I add it as a filter on the page. 

 

and here's how my MTD measure is written: MTD Deposits = CALCULATE(SUM(meAccountTransaction[Deposits]), DATESMTD(meCalendar[DateValue])) 

@majdkaid22 I cannot replicate that. It works fine in my test. Are you sure there's not another visual level filter on that blank card?

 

Silly question, you do have a table relationship between meAccountTransaction[TransactionDate] and meCalendar[DateValue] right?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@KHorseman Yes between ValueDate in Calander and TransactionDate in meAccountTransaction 

 

model.JPG

@KHorseman apologies, the relationship is between CalendarDateID and PostingDateID

 

I think my DateValue and TransactionDate need to have the same format before they can be linked together no? 

 

If the relationship then between those 2, it should be all solved? 

 

 

mode3.JPGmodel5.JPG

@majdkaid22 almost. When you create a second relationship between the same two tables the new relationship is inactive. This means that you have to explicitly tell formulas to use it. By default all formulas will use that first relationship. So your MTD formula should now be:

 

MTD Deposits = CALCULATE(
	SUM(meAccountTransaction[Deposits]),
	DATESMTD(meCalendar[DateValue]),
	USERELATIONSHIP(
		meAccountTransaction[TransactionDate],
		meCalendar[DateValue] 
	)
)




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@KHorseman 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!

The columns in the relationship must be the same. If one is datetime and the other is date only, there will be zero matches between the two columns.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Can you please post the screenshots of your data model, sample data from all the tables for the further investigation.

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.