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
chakrabmonoj
Helper II
Helper II

add YTD and MTD to sales figures

I am trying to compare sales for two separate years on ytd and mtd basis. But the first month in my data is jul and not jan; so for YTD current year it sould add jul-dec and similarly for the previous year. For MTD it should compare whatever is the latest month for the current year.

 

I have tried using datesinperiod with firstdate and also datesytd - but its not working :

 

BI error.JPG

My data looks like this :

data screenshot.JPG

 

1 ACCEPTED SOLUTION

Oh my friend, that's be called evaluation context ^_^ my recommendation is forgetting about breaking down formula and start to learn DAX, you will understand it correctly with some books about DAX or some completely articles.

 

View solution in original post

28 REPLIES 28

Hi, could you please send me the file for reference? , thanks a lot!

Hey,

 

Which part of this formula is actually returning the year - 2015 for pytd and 2016 for ytd? 

 

very elegant and interesting formula though...have learnt a lot

Hi @chakrabmonoj,

 

Dates[Date] <= MAX(Dates[PY Date]) is for cummulative

Dates[Year]=MAX(Dates[Year])-1<< this will return 2015 in that context

Dates[Year]=MAX(Dates[Year]) << this will return 2016 in that context

 

but when I am just calculating separate measures, with each of these formulae, this is what I am getting :

 

1. Max(dates[date]) is returning 31,12,2020

2. Year(max(dates[date] is returning 2020

 

so I am getting a little confused as to the above two, within your formula is returning 2015 and 2106

 

sorry to continue to bug you - may be its very simple for you to understand, but I am really trying to get under the skin of this formula as this is going to solve a lot of issues for me going forward and I really want to understand this in detail.

 

If you could just take some time and break down each part of rhe formula and show&tell, what will each part return as a value.

 

thank you so much

Oh my friend, that's be called evaluation context ^_^ my recommendation is forgetting about breaking down formula and start to learn DAX, you will understand it correctly with some books about DAX or some completely articles.

 

Hi @chakrabmonoj,

 

You could update in any cloud storage and give me the link

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.