cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Solution Specialist
Solution Specialist

YTD last year DAX

Hi,

 

I have question about YTD Last year DAX formula. Even though I have read many blogs and topics here in community, I still don't know how to solve this issue.

Suppose I have data like below.

 

IDDateAmount
101.01.201610
202.01.201615
303.01.201611
410.01.20168
520.02.20167
601.01.201710
702.01.201712
803.01.20179

 

I would like to figure up YTD value of amount for actual data (year 2017) and then YTD value for corresponding period previous year.

 

For YTD I use formula:

 

YTD = CALCULATE(SUM(Data[Amount]);DATESYTD(Date[Date]))

 

For YTD LY I use formula:

 

YTD LY = CALCULATE([YTD];SAMEPERIODLASTYEAR(Date[Date]))

 

Then when I use these formulas in PBI, I have got incorrect value for YTD LY. I expect value 36 (dates: 1.1. - 3.1. 2016), but in total I have got 44 (it also calculates Amount 8 in 10.1.2016) - so it gives me period of january 2016, but I want only the corresponding days from 2017.

But YTD LY value in pic below on row for 3.1.2017 is OK.

 

YTD_LY.PNG

 

Does anybody know the solution?

 

Thanks.

Regards.

Pavel

1 ACCEPTED SOLUTION

@PavelR

 

It should work fine in Power BI.

You didn't have any dates in your Data table beyond 3 Jan 2017 did you?

 

Here is a sample PBIX file with your data posted above where the measure is working:

PBIX file

 

I made a small change to the YTD measure so that it is not displayed after the max date in Data table, but the YTD LY measure is the same as I posted.

 

Have a play with that - there must be some difference in your model if it is not working.

 

Owen


Owen Auger

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

Connect on Twitter
Connect on LinkedIn

View solution in original post

50 REPLIES 50
Regular Visitor

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

First of, thanks for all the help on the issue @OwenAuger however, I have a problem which I couldn't resolve at all since I am kind of beginner with DAX.

 

My dataset has unique Sales Year-Month-Day values from 01.01.2016 to 05.20.2017 in date format and was related with a Calendar Date column which ends at 05.20.2017 as well since it's the last data collection date.

 

First I was struggling to compare MoM for 2016 and 2017 sales. For example, May of 2017 contains only 20 days of data and I wanted to compare the same date range for May of 2016; however, it was fetching all the 2016 data till the end of the year. I solved that issue with your explanation.

 

For this year sales I used: 

 

This Year Sales = CALCULATE(SUM([SalesRevenue]);DATESYTD('Calendar'[Date].[Date]))

 

And for last year sales:

Last Year Sales = 
VAR DataMaxDate =
    CALCULATE ( MAX ( 'Calendar'[Date] ); ALL ( 'Calendar'[Date] ) )
RETURN
    CALCULATE (
        [This Year Sales];
			SAMEPERIODLASTYEAR (
            INTERSECT (
                VALUES ( 'Calendar'[Date].[Date] );
                DATESBETWEEN ( 'Calendar'[Date].[Date]; BLANK (); DataMaxDate )
            )
        )
    )

  

Since my intention was comparing apples to apples, this was the result I was looking for. I finally was able compare the first 20 days of May 2016 and first 20 days of May 2017. 

 

 

However, now I am not able to visually filter any of the months. I mean, when I click Last Year Sales, it's automatically accumulating This Year Sales as well. Please see below:

 

My intention is:

1. To be able to individually filter every year's sales like I was able to before.

2. If I cannot filter individually because of the formulas I used, I am kind of looking for a new way to compare last year's and this year's sales. Like I mentioned, last data date must be taken into account for last year's datas.

3. I created a different Calendar.[Date] and columns related with my sales date data. (Format is in date like 01.01.2016)

 

Thanks for all the help, rgrds,

@Blackworms

When you have a clustered column chart with two or more measures as Value fields, visual filters resulting from clicking on the bars are applied to the axis field.

 

In your example, the visual filter is applied to the month dimension on the axis to include only "May", but both measures are still visible.

 

You can de-emphasise bars for a particular measure by clicking on the legend, but this can't be done simultaneously with an axis dimension filter. Also, I don't think this 'filters' any other visual in any way.

 

Also I couldn't work out what the $3.68m represents. Are you saying that's the total of Last Year Sales and This Year Sales?

 


Owen Auger

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

Connect on Twitter
Connect on LinkedIn
Super User I
Super User I

@PavelR

 

It sounds like you want your YTD LY measure to detect the last date that appears in your Data table, and only translate dates up to that date back to last year within SAMEPERIODLASTYEAR.

 

You can use a pattern like this (note the distinction between Data[Date] which I assume is a column of Data, and Date[Date]):

 

YTD LY (limited by last date in Data table) =
VAR DataMaxDate =
    CALCULATE ( MAX ( Data[Date] ), ALL ( Data ) )
RETURN
    CALCULATE (
        [YTD],
        SAMEPERIODLASTYEAR (
            INTERSECT (
                VALUES ( Date[Date] ),
                DATESBETWEEN ( Date[Date], BLANK (), DataMaxDate )
            )
        )
    )

DataMaxDate is the global maximum date in your Data table. You can define it differently if you want to apply different logic.

 

Cheers,

Owen 🙂


Owen Auger

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

Connect on Twitter
Connect on LinkedIn
Solution Specialist
Solution Specialist

Hi @OwenAuger, thanks for your response,

 

when I implement your measure to PBI I get the same value of 44, as in previous YTD LY measure. Am I wrong with something? Is there some additional prerequisite in PBI or something?

 

Thanks and regards

Pavel

 

@PavelR

 

It should work fine in Power BI.

You didn't have any dates in your Data table beyond 3 Jan 2017 did you?

 

Here is a sample PBIX file with your data posted above where the measure is working:

PBIX file

 

I made a small change to the YTD measure so that it is not displayed after the max date in Data table, but the YTD LY measure is the same as I posted.

 

Have a play with that - there must be some difference in your model if it is not working.

 

Owen


Owen Auger

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

Connect on Twitter
Connect on LinkedIn

View solution in original post

@Owen 

 

It's working fine, but i want to show whatever date values are there in the last year. In below example i want to display 10.01.2016 as well

 


ID Date Amount
1 01.01.2016 10
2 02.01.2016 15
3 03.01.2016 11
4 10.01.2016 8
5 01.01.2017 10
6 02.01.2017 12
7 03.01.2017 9

 

Is it possible to show please let me know

I used the following formula to find YTD and YTD LY

YTD = CALCULATE(SUM('data'[konto]),
FILTER('year',[Date]>=DATE(YEAR(TODAY()),1,1) && [Date]
<=(DATE(YEAR(TODAY()),MONTH(TODAY()),1)-1)))

 

YTD LY = CALCULATE(SUM('data'[konto]),
FILTER('year',[Date]>=DATE(YEAR(TODAY())-1,1,1) && [Date]
<=(DATE(YEAR(TODAY())-1,MONTH(TODAY()),1)-1)))

when I select year 2018 It only showes me YTD, how can I fix this to look both YTD and YTD LY when year 2018 is selected?

Hi,

 

What exactly are you trying to do?  Describe your question in detail and share some data.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Just wanted to say thanks @OwenAuger for your solution . Saved me lot of anxiety and time 🙂

 

@OwenAuger, is it possible to calculate YTD based on fiscal year instead of calendar year (i.e. Starting at Apr 1, instead of Jan 1)?

Hi,

 

The second input of the DATESYTD function is the year end date.  See here.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@OwenAuger: I tried your DAX formula and it works, to an extent. It works for the current year. I'm looking to display a table that shows the following three columns: [Year], [YTD], [YTD Last Year].

 

To test your formula I built a simple Excel sheet with two columns: [Sale Date] and [Sales] and populted it with every date between 1/1/2015 and today (8/18/2017) and $1.00 of Sales on each day (hey, makes it easy to test, right?). This table is joined to a fully populated Date table with dates ranging back and forward several years.

 

After adjusting your formula and building my table, I see this:

2017-08-18_10-35-11.jpg

 

2017 is showing correct LYTD value of 231 (one over this year because of Feb. 29 2016) but Year 2016 should show 230. Instead it show 365.

 

Seems LYTD should be a VERY simple calculation in DAX. (MDX had the function ParallelPeriod that made this SUPER EASY!) I've spent hours pouring over blogs, forums and technet and still can't seem to get it.

 

Frustrated.

 

Thanks in advance.

@ToddChitt

 

The requirement from the original post was to createa a "YTD Last Year" measure that restricts the dates for "last year" just in the special case where the current date filter context goes past the last date for which data exists.

 

The reason for even wanting such a measure is that the built-in behaviour of time intelligence functions is to shift the current Date table filter context in some way. So if 2017 is selected on the Date table, the filter context is 1 Jan 2017 to 31 Dec 2017, and "last year" is 1 Jan 2016 to 31 Dec 2016, regardless of whether any data exists relating to particular dates within those years.

 

It sounds like you're wanting something slightly different...

 

Is it correct that you want you YTD Last Year measure to always look at the number of days for which data is present in the "latest" year (2017 in your example), and restrict the date context accordingly when shifting back dates not only from 2017 but also 2016 or any earlier year?

 

Is this because in your model, "YTD" is defined relative to the actual date when the model is refreshed (i.e. today in the real world), rather than the current date filter context?

 

 

Would you still want the YTD measure to behave as they currently are, or also restrict all years based on the latest year?

 

It can all be done, just a matter of defining the desired behaviour of the measures.

 

Regards,

Owen


Owen Auger

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

Connect on Twitter
Connect on LinkedIn

@OwenAuger:

Thanks for the reply.

>Is it correct that you want you YTD Last Year measure to always look at the number of days for which data is present in the "latest" year (2017 in your example), and restrict the date context accordingly when shifting back dates not only from 2017 but also 2016 or any earlier year?<

Not exactly. YTD should look at the current date, and apply that respective end date to the previous years. If today is the 22nd of August, there is no fact data beyond today, and for prior years I would expect to see data from 1/1/2016 up through 8/22/2016, and likewise 1/1/2015 up through 8/22/2015, and on down the years. Come September 2, 3, and 4, (Labor Day holiday weekend in the US) there will be no fact data generated, but the filter context should be current date, not the last date for which we have data.

 

I'm usually pretty good with DAX but this one seems to have me stumped. I do have YTD measure defined as follows:

YTD = TOTALYTD ( [Base Measure], 'Dates'[Date] )

 

Thanks in advance.

-Todd

 

@ToddChitt

 

Got it...the reason your particular measure is not straightforward is that the built in time intelligence functions are designed to perform calculations relative to the selection on the Date table rather than TODAY().

 

There are multiple ways you could handle this - here is one possibility (code below).

 

The logic I have used is to

  1. Get the month/day of TODAY()
  2. Shift that into the currently filtered year (based on the maximum selected date)
  3. Create two date filters: YTD based on the shifted date in step 2 & YTD based on the maximum selected date
  4. Calculate Sales with Date filtered to the intersection of the filters from step 3

The reason for the intersection in step 4 is to handle cases where you are filtering at a lower level than year. So if today is 25-Aug-2017 and you have filtered on Jul-2017 in a visual, the measure would return sales from 1-Jan-2017 to 31-Jul-2017. However if you selected any month from Aug-2017 to Dec-2017, you would get sales from 1-Jan-2017 to 25-Aug-2017.

 

Sales Amount YTD based on current date = 
VAR Today =
    TODAY ()
VAR TodayMonth =
    MONTH ( Today )
VAR TodayDay =
    DAY ( Today )
VAR MaxDateSelectedYear =
    YEAR ( MAX ( 'Date'[Date] ) )
VAR TodayShiftedToSelectedYear =
    DATE ( MaxDateSelectedYear, TodayMonth, TodayDay )
RETURN
    CALCULATE (
        [Sales Amount],
        DATESYTD ( 'Date'[Date] ),
        CALCULATETABLE (
            DATESYTD ( 'Date'[Date] ),
            'Date'[Date] = TodayShiftedToSelectedYear
        )
    )

Regards,

Owen 🙂


Owen Auger

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

Connect on Twitter
Connect on LinkedIn

Hi Owen,

 

Is it possible to get the logic below with the exception that it calcultates sales amount with a start date based on selected value from a date slicer?

 

Sales Amount YTD based on current date = 
VAR Today =
    TODAY ()
VAR TodayMonth =
    MONTH ( Today )
VAR TodayDay =
    DAY ( Today )
VAR MaxDateSelectedYear =
    YEAR ( MAX ( 'Date'[Date] ) )
VAR TodayShiftedToSelectedYear =
    DATE ( MaxDateSelectedYear, TodayMonth, TodayDay )
RETURN
    CALCULATE (
        [Sales Amount],
        DATESYTD ( 'Date'[Date] ),
        CALCULATETABLE (
            DATESYTD ( 'Date'[Date] ),
            'Date'[Date] = TodayShiftedToSelectedYear
        )
    )

 

At the moment it always return YTD from 01-Jan to max TODAY or to a selected date less than TODAY. But when selecting the date range with start 10-Jan-19 to 17-Jan-19 it still returns the sales amount for 01-Jan-19 to 17-Jan-19. 

 

 

Regards,

Robert

Hi @Robert_Jensen,

 

That mean you are not after really YTD rather data between selected dates. If you select full year, you want actually YTD data and if you select some mid range of dates, you want data for those dates only, hence aggrigation for selected dates and not YTD. 

In that case, don't use DATESYTD because this will always start from first day of selected year. May be something like below:

Sales Amount YTD based on current date =
CALCULATE (
    [Sales Amount],
    FILTER ( 'Date', 'Date'[Date] >= MIN ( 'Date'[Date] ) ),
    FILTER ( 'Date', 'Date'[Date] <= 'Date'[Date] )
)

emudria

 

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
 

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]))))

 

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors