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

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
v-piga-msft
Resident Rockstar
Resident Rockstar

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

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.
v-piga-msft
Resident Rockstar
Resident Rockstar

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

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.
AlB
Super User
Super User

Hi @Anonymous

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

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.