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
Anonymous
Not applicable

SUM MTD Not working through DateTable

Hi,

LeadCount MTD = 
CALCULATE(SUM(fLeads[LeadCount]), DATESMTD(dDate[Date])) is not working
But if I do directly LeadCount MTD = 
CALCULATE(SUM(fLeads[LeadCount]), DATESMTD(fLeads[DATE_KEY_LEAD])) it works fine.
 
Same thing if I try using:
TOTALMTD(SUM(fLeads[LeadCount]), dDate[Date]) is not working.
TOTALMTD(SUM(fLeads[LeadCount]), fLeads[DATE_KEY_LEAD]) is working fine.

Another measure works fine if I use my Datetable.
Yesterday =CALCULATE( SUM(fLeads[LeadCount]), dDate[Date] = TODAY()-1)
 
I am going mental over here - What am I doing wrong? 

The relationsship must be setup correctly if one measure is working fine. 

Looking forward to your help.
 
pbi.jpg

 

11 REPLIES 11
Anonymous
Not applicable

@Anonymous ,

 

seems like type mismatch with data tabel and fLeads[DATE_KEY_LEAD], 

 

make sure fLeads[DATE_KEY_LEAD] is type of date.

Anonymous
Not applicable

Hi @Anonymous 

 

Seems like it is, right?

 

dDate.jpgdDate2.jpg

 

Anonymous
Not applicable

@Anonymous ,

 

Seems like not 🙂

Anonymous
Not applicable

@Anonymous 
Why not?
The field is stated as a date and the Yesterday function is working fine. 

Could you please explain what am I missing?

Anonymous
Not applicable

@Anonymous , 

 

You have define your types correctly, 

as a cheeky solution you can use the same formula as in Yeaterday - 1 (as it works).

 

Instead of giving static value (2) in the formule

dDate[Date] = Today() - 2

you can use a variables to calculate the number of days in the month and pass it to there. 

 

So your LeadCountMTD would be

 

VAR Var1 = Count Number of MTD

CALCULATE(
     SUM(fLeads[LeadCount),

     dDate[Date] = TODAY() - Var1

)

 

 

 

 

 

I have being sarcastic 🙂

V-pazhen-msft
Community Support
Community Support

@Anonymous 

What is error message or what wrong result you get with the dax that is not working.

 

Regards
Paul

Anonymous
Not applicable

@V-pazhen-msft 

 

Thank you so much for replying.

My result is (blank) 

amitchandak
Super User
Super User

@Anonymous , Is the date table connected to your date table.

Date table marked as a date table .

You have all dates in date table.

 

The last one but most important. If you do not select a date on-page. The last date for MTD is the last date of the calendar.  Seem like this the case as you are getting data for the table date because it end in this month

 

refer : https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e

Anonymous
Not applicable

Hi @amitchandak 

Yes I have dates for the whole 2020 in the Date table.

It is marked as a date table.

Yes there is data for the period. 

 

One of the measure is working fine through it. 

@Anonymous , When you do select a date on you page .

 

 

TOTALMTD(SUM(fLeads[LeadCount]), fLeads[DATE_KEY_LEAD]) . End date of your MTD here is end date of you data coming from transaction table. so you are getting it 
 
TOTALMTD(SUM(fLeads[LeadCount]), dDate[Date]) is not working. End date ne date of calendar which may dec-2020.
 
Go and plot. MTD with date calendar with month year and check. 
Anonymous
Not applicable

Hi @amitchandak 

I have uploaded a video to youtube.

 

Youtube_Video - Not the best result but maybe you can slow the video down.

First I show the result 4. This result works fine with the dDate.

Yesterday - 1 =CALCULATE( SUM(fLeads[LeadCount]), dDate[Date] = TODAY()-1

Therefore dDate[Date] and fLeads[DATE_KEY_LEADS] must be the correct format right? Else why would the result not be blank?

 

After I show CALCULATE(SUM(fLeads[LeadCount]), DATESMTD(dDate[Date])) is not working.

 

Please help.

 

 

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.