Forum Discussion

littlemojopuppy's avatar
littlemojopuppy
Icon for Community Champion rankCommunity Champion
8 years ago
Solved

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!

Need Help
  • littlemojopuppy's avatar
    littlemojopuppy
    7 years ago

    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!

12 Replies

    • littlemojopuppy's avatar
      littlemojopuppy
      Icon for Community Champion rankCommunity Champion
      8 years ago

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




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