Forum Discussion
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.
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.
11 Replies
- amitchandak
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
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.
- amitchandak
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 itTOTALMTD(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.
