Power BI Desktop DATESMTD Measure

[jamezcua9]

Hello,

I'm fairly new to Power BI and currently using PBI Desktop to develope and publish.  I'm having trouble with a measure that is not calculating the date corretly.  I have 2 queries with a relationship connected together.  

This is my measure : 

MonthTarget = CALCULATE(SUM('Cluster Plan'[AVERAGE_PER_DAY_US]),DATESMTD('Cluster Plan'[PLAN_DATE]),FILTER('Cluster Plan','Cluster Plan'[CLUSTER_PLAN_DATE]<TODAY()))

Since we now switched over to March 2019, the measure is calculating the same target KPI on every single date. 

Any idea on how I can accomplish the correct measure total based on the date/month?  

[bogomda · Accepted solution]

DATESMTD resets counting every month. you probably don't have any records  for "Cluster Plan"[AVERAGE_PER_DAY_US] for months of March yet.

Another problem could be that DATESMTD is not applied to calendar table (table which is related to fact table with dates starting from Jan 1 through Dec 31 and daily granularity).

without seeing that model it's hard to fix the problem.

[v-chuncz-msft · Accepted solution]

@jamezcua9 ,

For any time intelligence function, you could implement a custom DAX formula.

https://www.sqlbi.com/articles/time-intelligence-in-power-bi-desktop/

[bogomda · Accepted solution]

DATESMTD resets counting every month. you probably don't have any records  for "Cluster Plan"[AVERAGE_PER_DAY_US] for months of March yet.

Another problem could be that DATESMTD is not applied to calendar table (table which is related to fact table with dates starting from Jan 1 through Dec 31 and daily granularity).

without seeing that model it's hard to fix the problem.

[jamezcua9]

I just added the Fact table DimCalendar. 

My cluster plan tables does not have every day granualarity, but does have YYYYWW column in common.  Still it does not give me what I need.  

[bogomda]

it will be hard to fix your issue not seeing sample model.

[Greg_Deckler]

See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...