cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Phaneendra Frequent Visitor
Frequent Visitor

Need Growth values compated to MTD and YTD

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

3 REPLIES 3
Super User
Super User

Re: Need Growth values compated to MTD and YTD

Hi @Phaneendra

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

Community Support Team
Community Support Team

Re: Need Growth values compated to MTD and YTD

Hi @Phaneendra,

 

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

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community Support Team
Community Support Team

Re: Need Growth values compated to MTD and YTD

Hi @Phaneendra,

 

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

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.