cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Re: YTD last year DAX

Hi @OwenAuger 

 

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 (
            DATEADD(
            INTERSECT (
                VALUES ( kalendarz[Date].[Date] );
                DATESBETWEEN ( kalendarz[Date].[Date]; BLANK (); TODAY() )
            ); -1;MONTH)
        )
    )
Highlighted
Community Champion
Community Champion

Re: YTD last year DAX

@mat_k 

Just clarifying your requirements...

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

Did I answer your question? Mark my post as a solution!

Connect on Twitter
Connect on LinkedIn
Highlighted
Helper I
Helper I

Re: YTD last year DAX

@OwenAuger 

  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.

 
Community Champion
Community Champion

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

Did I answer your question? Mark my post as a solution!

Connect on Twitter
Connect on LinkedIn
Highlighted
Helper I
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

 

2019-04-10 10_35_29-ly_ytd_indicator - Power BI Desktop.png2019-04-10 10_35_20-ly_ytd_indicator - Power BI Desktop.png

Highlighted
Community Champion
Community Champion

Re: YTD last year DAX

@mat_k Thanks for testing this out 🙂

 

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

Uploaded corrected version of your file here.

 

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

Did I answer your question? Mark my post as a solution!

Connect on Twitter
Connect on LinkedIn
Highlighted
Helper I
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 .
 

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors