cancel
Showing results for
Did you mean:
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

New Contributor

## Re: add YTD and MTD to sales figures

```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

) )```

I have sent you updated file for reference

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

New Contributor

## Re: add YTD and MTD to sales figures

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

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

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.

Frequent Visitor

## Re: add YTD and MTD to sales figures

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

Frequent Visitor

## Re: add YTD and MTD to sales figures

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

Highlighted
Frequent Visitor

## Re: add YTD and MTD to sales figures

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

Announcements

#### Challenge: Can You Solve These?

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

#### Community News & Announcements

Get your latest community news and announcements.

#### 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.

#### Win Power BI Swag with Community Kudopalooza!

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

Top Kudoed Authors
Users Online
Currently online: 336 members 3,335 guests
Recent signins: