Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
littlemojopuppy
Community Champion
Community Champion

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

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
littlemojopuppy
Community Champion
Community Champion

Chart 1.PNGChart 2.PNG

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!

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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...

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)

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?

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!

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.

Hi,

 

Does this work?

 

YTD Total = CALCULATE( [Item Count], DATESBETWEEN( 'Calendar'[CalendarDate], DATE(YEAR(TODAY()),1,1),TODAY()))


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

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!

Ha!  The underlying problem was really that in my ETL/data shaping, the date table has dates going out to 2024 or 2025 (for accountants forecasting).  I was pulling in "this year" but after playing with this across numerous dashboards I think the TOTALYTD function really pulls up to the last date available in the date table (or source of dates).

Changing the ETL/data shaping process to include only to YTD instead of this year solved all my problems.

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.