cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Super User I
Super User I

Re: YTD last year DAX

@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
Highlighted
Responsive Resident
Responsive Resident

Re: YTD last year DAX

@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

 

Highlighted
Super User I
Super User I

Re: YTD last year DAX

@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
Highlighted
Advocate II
Advocate II

Re: YTD last year DAX

Hello Owen, thanks for all the work on this thread. It has helped me immensely already. Your solution in the previous post works "almost" works for me, except for one error that I can't even begin to imagine the source of. 

 

My data has 6 years, and the "to date" shows up as it should for every year except for 2016. And 2016 is just blank. This isn't a problem with my data, as 2016 shows up just fine in all other visuals/scenarios. Does anything come to mind as to what could be going on?

 

EDIT: I jumped the gun replying here, I think today's date in 2016 was over the weekend and didn't have any data, but adding the that date via a calendar table with all dates seems to have fixed it. 

 

Thanks again, Owen! 

Highlighted
Frequent Visitor

Re: YTD last year DAX

@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)?

Highlighted
Super User III
Super User III

Re: YTD last year DAX

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/
Highlighted
Frequent Visitor

Re: YTD last year DAX

@OwenAuger

 

I tried to use the measure you created below to solve a similar problem I am having. My issue though is that I'd only like YTD through the end of the previous month. So not including any of current month revenues. How would I modify that to provide YTD through the previous month for current year and previous years? I'm still very beginner and I've never used VAR before, so I was trying to figure out how to modify that part but can't figure it out. 

Highlighted
Super User I
Super User I

Re: YTD last year DAX

@SuperDatagirl

 

Sure - the way I would do it is by changing the  TodayShiftedToSelectedYear to be the end of the previous month.

This will mean that if TODAY() is in January, you will get a blank measure, and in February you will just see January etc.

 

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 =
    EOMONTH ( DATE ( MaxDateSelectedYear, TodayMonth, TodayDay ), -1)
RETURN
    CALCULATE (
        [Sales Amount],
        DATESYTD ( 'Date'[Date] ),
        CALCULATETABLE (
            DATESYTD ( 'Date'[Date] ),
            'Date'[Date] = TodayShiftedToSelectedYear
        )
    )

Could you try this out and post back if you need more help?

 

Regards,

Owen


Owen Auger

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

Connect on Twitter
Connect on LinkedIn
Highlighted
Frequent Visitor

Re: YTD last year DAX

@OwenAuger

 

That worked!! Thank you SO much. Sadly, I think I spent 2 days working on this and still couldn't figure it out.

Highlighted
Helper V
Helper V

Re: YTD last year DAX

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

 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors