cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
chakrabmonoj Regular Visitor
Regular Visitor

Re: add YTD and MTD to sales figures

Hey,

 

I am kind of getting a hang of your formula - but still lost as to how this formula is (which part of it is designed) to return the values for the year 2015 and 2016...which part of the formula is designed to filter/extract that from dates[data]?

Is it possible to do keep YTD (for 2016) and PYTD (for 2015) for months{jul-dec} as two separate measures so that I can chart them by category side by side and with a line graph showing the change? [ref. the page called "L3L6"]

thanks

tringuyenminh92 New Contributor
New Contributor

Re: add YTD and MTD to sales figures

Hi @chakrabmonoj,

 

Please try with:

Prev YTD = CALCULATE(sum(table1[VALUE]), 
                 FILTER(ALL(Dates), SUM(table1[VALUE])>0 &&
					 Dates[Date] <= MAX(Dates[PY Date]) && Dates[Year]=MAX(Dates[Year])-1
 			&& Dates[Date].[MonthNo] >= FIRSTNONBLANK(Configuration[Month],1)
 
					 ) )

 

YTD = CALCULATE(sum(table1[VALUE]), 
                 FILTER(ALL(Dates),
					SUM(table1[VALUE])>0 && Dates[Date]<=MAX(Dates[Date])
					&& Dates[Date].[Year]=YEAR(MAX(Dates[Date]))
&& [Prev YTD]>0					
 
					 ) )

Screenshot 2017-02-04 22.02.39.png

 

I have sent you updated file for reference

 

chakrabmonoj Regular Visitor
Regular Visitor

Re: add YTD and MTD to sales figures

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

tringuyenminh92 New Contributor
New Contributor

Re: add YTD and MTD to sales figures

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

 

chakrabmonoj Regular Visitor
Regular Visitor

Re: add YTD and MTD to sales figures

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

tringuyenminh92 New Contributor
New Contributor

Re: add YTD and MTD to sales figures

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

diegorumo Frequent Visitor
Frequent Visitor

Re: add YTD and MTD to sales figures

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

diegorumo Frequent Visitor
Frequent Visitor

Re: add YTD and MTD to sales figures

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

Highlighted
diegorumo Frequent Visitor
Frequent Visitor

Re: add YTD and MTD to sales figures

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

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 336 members 3,335 guests
Please welcome our newest community members: