Need Growth values compated to MTD and YTD

[Anonymous]

Hi Team,

We have last 2 years of data. with date, year and month column. Now I need to create a measure to find out YTD growth compared to last year and current year, MTD growth compared to Previous month of last year and previous month of current year.

I have done below measures and it is working fine when we dont select any store name.

# MTD CY Sales = CALCULATE([# Act Sales],DATESMTD('Growth-Rank'[Sale Date]))
# MTD PY Sales = if(LASTDATE(CalendarTable[Date]) > TODAY(), BLANK(),CALCULATE([# Act Sales],DATESMTD(DATEADD('Growth-Rank'[Sale Date],-1,YEAR))))
# MTD Growth = [# MTD CY Sales] - [# MTD PY Sales]

# YTD CY Sales = CALCULATE([# Act Sales],DATESYTD('Growth-Rank'[Sale Date]))
# YTD PY Sales = if(LASTDATE('Growth-Rank'[Sale Date])>TODAY(),BLANK(),CALCULATE([# YTD CY Sales],DATESYTD(DATEADD('Growth-Rank'[Sale Date],-1,YEAR))))
# YTD Growth = [# YTD CY Sales] - [# YTD PY Sales]

Every thing is working fine but when I drill down I am getting errors.

Regards

Phaneendra

[v-piga-msft]

Hi @Anonymous,

Have you solved your problem?

If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, please feel free to ask.

Best Regards,

Cherry

[v-piga-msft]

Hi @Anonymous,

From your formulas, it seems that you use time intelligence functions to caluclate the YTD and MTD. Based on the example of DATESMTD, we could see the date in DATESMTD fucntion is another date column in Date table.

DATESMTD(DateTime[DateKey])) 

So you could create a Date table with Calendar function and create the relationship between the tables and try again.

If you still need help, please share a dummy pbix file which can reproduce the scenario, so that we can help further investigate on it? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.)

Best Regards,

Cherry

[AlB]

Hi @Anonymous

It'd be easier for people to spot what the issue is if you shared your pbix file