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

YTD Running Totals

Results #1Hi!  Been trying to figure this out for the better part of a day and a half now, and this makes no sense at all to me.  Strong SQL background so DAX has been hard to learn...

Trying to calculate a YTD running total (through the most recent date for which there is data, up to and including today).  Found a very similar snippet of code elsewhere in this forum, modified how I thought appropriately, and almost works fine.  Problem is that the graph continues through 12/31 and I only want it to display through today.

Here's the code and the graph is chart 1

YTD Total = CALCULATE ( 
        COUNT('Inventory Support Request'[GUID]), 
        FILTER (ALL('Calendar'),
            ('Calendar'[CalendarYear] = MAX('Calendar'[CalendarYear])) && 
            ('Calendar'[CalendarDate] <= MAX('Calendar'[CalendarDate]))
        )
    )

I thought by modifying the code as follows that it would limit to where there is data for this year (beginning in July) and up to and including today.  It's not...it's taking today's counts and applying it to the entire year.

YTD Total = CALCULATE ( 
        COUNT('Inventory Support Request'[GUID]), 
        FILTER (ALL('Calendar'),
            ('Calendar'[CalendarYear] = MAX('Calendar'[CalendarYear])) && 
            ('Calendar'[CalendarDate] <= TODAY())
        )
    )

Could someone please offer some help with this?  And if anyone knows of resources on where to learn DAX that would also be helpful!

Thank you!

1 ACCEPTED SOLUTION

Accepted Solutions

Re: YTD Running Totals

No. Same result.  But this works...

YTD Total = 
    TOTALYTD(
        [Item Count],
        'Calendar'[CalendarDate],
        'Calendar'[CalendarDate] <= TODAY()
    )

I'm good.  Overthinking it!  Thank you for your help!

View solution in original post

12 REPLIES 12

Re: YTD Running Totals

Chart 1.PNGChart 2.PNG

Re: YTD Running Totals

Hi!  I still haven't given up on this!  But now I'm even more confused because the DAX functions don't seem to be working correctly.

I have two measures...Total YTD and Total PYTD,  Code below...

YTD Total = TOTALYTD(
	COUNT('Inventory Support Request'[GUID]),
	DATESYTD('Calendar'[CalendarDate])
)


PYTD Total = TOTALYTD(
	COUNT('Inventory Support Request'[GUID]),
	SAMEPERIODLASTYEAR(DATESYTD('Calendar'[CalendarDate]))
)

According to the Microsoft documentation the DATESYTD function "returns a table that contains a column of the dates for the year to date" (documentation here).  But it's not stopping at today's date (September 16 as I type this)...it's continuing out through the end of the year!  And the TOTALYTD function is supposed to "evaluate the year-to-date value of the expression" (documentation here).  So what gives?  The only thing I can think of is that my calendar table contains dates through 2019.

Second issue: the PYTD measure uses the SAMEPERIODLASTYEAR function...but it's not working backward in time, it's calculating the totals for the current year and then projecting forward one year!  I tried switching the functions so it reads like this and I'm getting the same result.

DATESYTD(SAMEPERIODLASTYEAR('Calendar'[CalendarDate]))



Capture.PNG
If anyone would please offer some advice/pointers on what's wrong, I would certainly appreciate it!

Highlighted
Community Support Team
Community Support Team

Re: YTD Running Totals

Hi @littlemojopuppy,

 

Kindly share your sample data or pbix to me.

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Super User
Super User

Re: YTD Running Totals

Might have more time to look at this later but in the mean time, see if this Quick Measure helps. 

 

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

 

I wrote it for numerous reasons, one of which is the frustration of dealing with the Time Intelligence functions.


I have book! Learn Power BI from Packt


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

Proud to be a Datanaut!

Re: YTD Running Totals

Hi!  Thank you both for replying...work has been crazy since the minute I walked in this morning.

I can't give the actual PBIX...the connections have server names/IP addresses included and I'd probably be fired for divulging information like that.  But that said, I'm postng a picture of the data model and the actual data (all fields in the fact table not relevant to these measures have been nulled).Capture.PNG


Greg, I'm looking at "Time Intelligence The Hard Way" right now...

Re: YTD Running Totals

So the YTD measure in "The Hard Way" is very similar to what I was trying with my original YTD measure (the code is at the beginning of the post) and it produced the exact same results as using Time Intelligence functions.  (I prefer the time intelligence because it seems "cleaner" and simpler to understand but honestly don't care as long as the error can be fixed)

Re: YTD Running Totals

Hello!  I found a solution for the YTD total here.  The measure is written like this:

YTD Total = IF( 
    MIN('Calendar'[CalendarDate]) <= 
        CALCULATE(
            MAX('Inventory Support Request'[Created]), 
            ALL('Inventory Support Request')
        ),
    CALCULATE(
        COUNT('Inventory Support Request'[GUID]),
        DATESYTD('Calendar'[CalendarDate])
    )
)

Here's what I don't understand, and I'm hoping someone can explain the logic to me...

I think what the measure is saying is if the minimum date in my date table is less than the maximum date in the fact table, then determine the maximum date in the fact table, then calculate the count of GUIDs in the fact table for all dates YTD.  My calendar table has dates going back to January of 1999 so the if condition will always be true.  How is that controlling the second calculate function telling it to stop?  If I remove the DAX from the IF statement up to the second CALCULATE, it reverts to calculating through the end of 2018???  How does this logic work?

Re: YTD Running Totals

I would think that the logical test should be if the MAX calendar date > MAX date from the fact table!  This seems like it's backwards!

Re: YTD Running Totals

So...I always thought that the previously posted code seemed unnecessarily complex to me.  As previously stated, it seems backward.  Logically, this code makes all the sense in the world to me

YTD Total = 
    TOTALYTD(
        [Item Count],
        DATESBETWEEN(
            'Calendar'[CalendarDate],
            STARTOFYEAR('Calendar'[CalendarDate]),
            TODAY()
        )
    )

 

But with that code, I end up with this
Capture.PNG
The Calendar table has dates going back to 1999 (I know...right???)  But that shouldn't matter.  The code is looking for dates between the start of this calendar year and today.  So why is it going back to the beginning of the calendar table?  The Microsoft documentation here says that "Istart_date is a blank date value, then start_date will be the earliest value in the dates column".  But code with STARTOFYEAR('Calendar'[CalendarDate]) for a calendar that has every date from Jan 1, 1999 on should begin counting at Jan 1, 2018, right?

I'm kind of stumped on this...if anyone has some suggestions on what to look at, I'd be very appreciative.

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)