cancel
Showing results for
Did you mean:
Highlighted
Helper I

Re: YTD last year DAX

Thanks for your tips on working with YTD LY formulas, they were very helpful for me. Currently I'm struggling with the following issue. I have ar report with a single Year Slicer, data is loaded with one month back period - there is a DATEADD formula which substracts 1 month from YTD LY to match the data.

I have a YTD and YTD LY measures, so for month 04.2019 they will show following data:

- 2019 YTD - sum of months 1,2,3 2019

- YTD LY - sum of months 1,2,3 2018

But when I change year slicer to show the last year data, which should be the full year data, DATEADD formula substracts -1 from months, so:

- 2018 YTD I have sum of all months 1 - 12 2018

- YTD LY - it shows data 11 months, 1 - 11 2017

I've tried to apply some IF formula to solve that using Calendar table, but I can't use calendar table for logical test.

Here is my YTD LY formula for this report:

```SomeIndicator YTD LY =
VAR DataMaxDate =
CALCULATE ( MAX ( kalendarz[Date].[Date] ); ALL ( kalendarz[Date] ) )
RETURN
CALCULATE (
[ SomeIndicator YTD];
SAMEPERIODLASTYEAR (
INTERSECT (
VALUES ( kalendarz[Date].[Date] );
DATESBETWEEN ( kalendarz[Date].[Date]; BLANK (); TODAY() )
); -1;MONTH)
)
)```
Highlighted
Super User I

Re: YTD last year DAX

Is this the behaviour you want?

1. When you filter on a year that is entirely in the past (i.e. 2018 or earlier as at today), you want to filter on the entire year for both the YTD and YTD LY measures?
2. When you filter on the current year (i.e. 2019 as at today), you want to adjust the YTD and YTD LY filters so include only months up to the previous month based on today's date (i.e. up to March if today is in April).

Owen Auger

Highlighted
Helper I

Re: YTD last year DAX

1. When you filter on a year that is entirely in the past (i.e. 2018 or earlier as at today), you want to filter on the entire year for both the YTD and YTD LY measures?
2. When you filter on the current year (i.e. 2019 as at today), you want to adjust the YTD and YTD LY filters so include only months up to the previous month based on today's date (i.e. up to March if today is in April).

1. Yes, e.g. when I filter on year 2018, I want to YTD measure show data for entire 2018, and YTD LY to show entire 2017.
2. That's right, when filter is set to current year, I want to include only YTD data up to previous Month for current and past year. E.g now is April 2019,  then YTD = 01,02,03 2019; YTD LY = 01,02,03 2018.

And, maybe it is important too, I have data in fact table by moth-year. So it is connected to Calendar Table by month-year key.

Highlighted
Super User I

Re: YTD last year DAX

Hi again @mat_k

I tinkered around with this one a bit.

Below are some measures that I believe should do what you want.

One note - I recommend you mark your kalendarz table as a Date Table, so you don't have to use the automatically generated date hierarchies with the dot-notation. The below measures assume you've done this.

The logic is essentially to limit the YTD date filter to only those dates up to and including the end of last month, using the INTERSECT function.

The IF(...) also ensures that the measures don't return a result for dates after the end of last month.

```SomeIndicator YTD =
VAR EndOfLastMonth =
EOMONTH ( TODAY (); -1 )
RETURN
IF (
MAX ( kalendarz[Date] ) <= EndOfLastMonth;
VAR DateFilterYTD =
INTERSECT (
DATESYTD ( kalendarz[Date] );
DATESBETWEEN ( kalendarz[Date]; BLANK (); EndOfLastMonth )
)
RETURN
CALCULATE ( [SomeIndicator]; DateFilterYTD )
)
```
```SomeIndicator YTD LY =
VAR EndOfLastMonth =
EOMONTH ( TODAY (); -1 )
RETURN
IF (
MAX ( kalendarz[Date] ) <= EndOfLastMonth;
VAR DateFilterYTD =
INTERSECT (
DATESYTD ( kalendarz[Date] );
DATESBETWEEN ( kalendarz[Date]; BLANK (); EndOfLastMonth )
)
VAR DateFilterYTD_LY =
SAMEPERIODLASTYEAR ( DateFilterYTD )
RETURN
CALCULATE ( [SomeIndicator]; DateFilterYTD_LY )
)
```

Let me know if this works for you.

Regards,

Owen

Owen Auger

Highlighted
Helper I

Re: YTD last year DAX

Hello @OwenAuger ,

Many thanks for your effort so far.

LY YTD measure works fine but only if I use months. For entire years, measeure is calculated only for past years, for current year is blank. And I need it to display for full year.

I've created pbix file with some random sample data for this case. Date is still as [Date].[Date] because my PowerBi app crashes after marking table as date table:)

LY YTD sample data

Highlighted
Super User I

Re: YTD last year DAX

@mat_k Thanks for testing this out 🙂

A small tweak is required to my original suggestion - thanks for spotting!

The part of both measures that tests whether the Max Date is <= EndOfLastMonth should actually test whether Min Date is <= EndOfLastMonth. i.e. it should be changed to

`IF ( MIN ( 'Calendar'[Date].[Date]) <= EndOfLastMonth`

With this change, you will see the expected result for years that are partially complete. The logic is that you want to return a result as long as the filtered date range begins on or before EndOfLastMonth.

Regards,

Owen

Owen Auger

Highlighted
Helper I

Re: YTD last year DAX

@OwenAuger big thank you! 🙂 It works great!

Highlighted
Regular Visitor

Re: YTD last year DAX

I used the update for YTD to

YTD_Sales = CALCULATE([Tot_Sales],filter(Amer_Date,Amer_Date[Date] >= MIN(Amer_Date[Date])),filter(Amer_Date,Amer_Date[Date] <= Amer_Date[Date]))
This works fine, the issue I run into is with LYTD. I have tried every soulution I could find. Most return all if last year and not a true LYTD. The closest I got to a working formula is :
LYTD_Sales =
IF(
LASTNONBLANK(Amer_Date[Date],[Tot_Sales])>MIN(Amer_Date[Date]),
CALCULATE([Tot_Sales],SAMEPERIODLASTYEAR(DATESBETWEEN(Amer_Date[Date],MIN(Amer_Date[Date]),LASTNONBLANK(Amer_Date[Date],[Tot_Sales])))))
The problem I discovered is if a customer did not order anything in a while his LYTD sales only shows Sales up to the last date for the current year and that is not a correct Year to Year comparison
What I need is a true comparison for this year today to today a year ago.
It would be great if someone knows how to solve this issue that I figured should be very common

Highlighted
Frequent Visitor

Re: YTD last year DAX

For those of you who have gone through all the formulas on the 5 pages so far on this blog and still haven't found a working solution for you....

I FINALLY found one that works for my data set from here: https://powerpivotpro.com/2016/01/year-to-date-in-previousprior-year/

Oye!

Here's the formula that ended up working for me:

```CALCULATE([Total Sales],
SAMEPERIODLASTYEAR(DATESBETWEEN(Calendar[date], STARTOFYEAR(Calendar[Date] ),
LASTNONBLANK(Calendar[Date], [Total Sales]))))```

Highlighted
Regular Visitor

Re: YTD last year DAX

Total YTD = CALCULATE(SUM(Sheet1[Value]),FILTER('DimDate','DimDate'[Year]))
This will  give individual year of YTD .

Announcements

Power Platform Community Conference

Check out the on demand sessions that are available now!

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021