YTD formula giving full year not actual YTD

[brittanyruffin]

I am looking to create a YTD formula (and Prior Year YTD). I've tried a few different variations of TOTALYTD and DATESYTD which have not worked. They end up giving me the full year values instead of a true YTD. I'm assuming this is because my visuals do not have a date axis. I also should mention I have a date table that includes 2023, 2024 and 2025 for historical and forecasting purposes in case that is relevant to the formula.

I have successfully created current quarter and full year formulas that do not require me to filter on the visual itself. i'd like to create the same with the YTD formulas if possible, but am fine with filtering to the current year if needed.

here is my matrix table (numbers removed) of my full year summary as example:

here is my full year formula for example:

Current Year Budget =

CALCULATE(

    SUM('vCorporateGandA'[Amount]),

    FILTER(

        ALL('CalendarTable'[Date]),

        YEAR('CalendarTable'[Date]) = YEAR(TODAY())

    ),

    'vCorporateGandA'[LedgerType] = "Budget"

)

and current quarter formula: 

Current Quarter Budget =

CALCULATE(

    SUM('vCorporateGandA'[Amount]),

    YEAR('CalendarTable'[Date]) = YEAR(TODAY()) &&

    QUARTER('CalendarTable'[Date]) = QUARTER(TODAY()),

    'vCorporateGandA'[LedgerType] = "Budget"

)

[AlexisOlson · Accepted solution]

Try this:

Current YTD Budget =
CALCULATE (
    SUM ( 'vCorporateGandA'[Amount] ),
    FILTER (
        ALL ( 'CalendarTable' ),
        YEAR ( 'CalendarTable'[Date] ) = YEAR ( TODAY () )
            && 'CalendarTable'[Date] <= TODAY ()
    ),
    'vCorporateGandA'[LedgerType] = "Budget"
)

[AlexisOlson · Accepted solution]

Try this:

Current YTD Budget =
CALCULATE (
    SUM ( 'vCorporateGandA'[Amount] ),
    FILTER (
        ALL ( 'CalendarTable' ),
        YEAR ( 'CalendarTable'[Date] ) = YEAR ( TODAY () )
            && 'CalendarTable'[Date] <= TODAY ()
    ),
    'vCorporateGandA'[LedgerType] = "Budget"
)

[brittanyruffin]

this worked! how can i get this for prior year as well? 

[AlexisOlson · Accepted solution]

There are lots of options including time intelligence functions like PARALLELPERIOD, DATEADD, or PREVIOUSYEAR.

You can also do it more directly but a bit ugly like this:

Prev YTD Budget =
CALCULATE (
    SUM ( 'vCorporateGandA'[Amount] ),
    FILTER (
        ALL ( 'CalendarTable' ),
        YEAR ( 'CalendarTable'[Date] ) = YEAR ( TODAY () ) - 1
            && 'CalendarTable'[Date] <=
                   DATE (
                       YEAR ( TODAY () ) - 1,
                       MONTH ( TODAY () ),
                       DAY ( TODAY () )
                   )
    ),
    'vCorporateGandA'[LedgerType] = "Budget"
)

[brittanyruffin]

any chance you can whip up a QTD and PY QTD formula too?

[AlexisOlson]

You can use the YTD ones and include another condition for the quarter like you have in your original post.

For example,

Current QTD Budget =
CALCULATE (
    SUM ( 'vCorporateGandA'[Amount] ),
    FILTER (
        ALL ( 'CalendarTable' ),
        YEAR ( 'CalendarTable'[Date] ) = YEAR ( TODAY () )
            && QUARTER ( 'CalendarTable'[Date] ) = QUARTER ( TODAY () )
            && 'CalendarTable'[Date] <= TODAY ()
    ),
    'vCorporateGandA'[LedgerType] = "Budget"
)

[brittanyruffin]

Hi Alexis,

Could you adjust this formula to start the month prior? For example, I will be refreshing the file on February 5th to report on January. Values for February are entered, but we don't want them reflected in the QTD formula until month end in March. 

[brittanyruffin]

This worked as well, thank you! 🙂