Previous YTD measure

[willatkinson]

Hi,

We have a YTD measure as follows which works perfectly:

Count of Case reference YTD =

TOTALYTD(COUNTA('Table'[Case reference]), 'DDT'[Date (DDT)])

How can we create a new measure that follows this exact logic, but calculates the previous YTD?

Thanks,

[Mauro89 · Accepted solution]

@willatkinson Your measure uses TODAY() which gives a fixed date, so it returns the same value regardless of the visual context. To make it work month-by-month in a visual, you need to use the date context from your visual instead of TODAY().

Replace with this:

Count of Case reference Previous YTD = 
CALCULATE(
    COUNTA('Table'[Case reference]),
    FILTER(
        ALL('DDT'[Date (DDT)]),
        YEAR('DDT'[Date (DDT)]) = YEAR(MAX('DDT'[Date (DDT)])) - 1 &&
        'DDT'[Date (DDT)] <= DATE(
            YEAR(MAX('DDT'[Date (DDT)])) - 1, 
            MONTH(MAX('DDT'[Date (DDT)])), 
            DAY(MAX('DDT'[Date (DDT)]))
        )
    )
)

How this works:

• MAX('DDT'[Date (DDT)]) gets the current date from your visual's filter context
• For each month in your visual, it calculates YTD for that same month in the previous year
• Example: If looking at March 2025, it shows Jan-Mar 2024

To use in a visual:

• Create a line/column chart
• X-axis: Month (from your date table)
• Y-axis: Your new Previous YTD measure
• Each month will show the cumulative total up to that month in the prior year

This replaces the static TODAY() with dynamic date context from your visual.

Best regards!

PS: If you find this post helpful consider leaving kudos or mark it as solution

[v-hjannapu]

Hi @willatkinson,

I would also take a moment to thank @Mauro89 ,  for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Regards,
Community Support Team.

[v-hjannapu]

Hi @willatkinson,
I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We are always here to support you.


Regards,
Community Support Team.

[Ashish_Mathur · Accepted solution]

Hi,

Assuming you have a Calendar Table and you have a year slicer and you have months dragged to a Table/Matrix visual

C = COUNTA('Table'[Case reference])

YTD C = calculate([C],DATESYTD(Calendar[Date],"31/12"))

YTD C (PY) = calculate([YTD C],samperiodlastyear(calendar[Date]))

Hope this helps.

[Mauro89]

Hi @willatkinson, 

you can try if these two measures work for your case. Check them with your actual data.

Option 1: Using SAMEPERIODLASTYEAR

Count of Case reference Previous YTD = 
CALCULATE(
    COUNTA('Table'[Case reference]),
    DATESYTD(SAMEPERIODLASTYEAR('DDT'[Date (DDT)]))
)

Option 2: Using DATEADD

Count of Case reference Previous YTD = 
CALCULATE(
    [Count of Case reference YTD],
    DATEADD('DDT'[Date (DDT)], -1, YEAR)
)

Best regards!

PS: If you find this post helpful consider leaving kudos or mark it as solution

[willatkinson]

Hi @Mauro89,

This didn't give me the figure I expected, but the following did:

CALCULATE(
COUNTA('Table'[Case reference]),
FILTER(
ALL('DDT'),
'DDT'[Date (DDT)] >= DATE(YEAR(TODAY()) - 1, 1, 1)
&& 'DDT'[Date (DDT)] <= DATE(YEAR(TODAY()) - 1, MONTH(TODAY()), DAY(TODAY()))
)
)

This gives me a year to date figure for the previous year, but I can only see a single figure, where I would like to see a month by month visual, how would this work?

[Mauro89 · Accepted solution]

@willatkinson Your measure uses TODAY() which gives a fixed date, so it returns the same value regardless of the visual context. To make it work month-by-month in a visual, you need to use the date context from your visual instead of TODAY().

Replace with this:

Count of Case reference Previous YTD = 
CALCULATE(
    COUNTA('Table'[Case reference]),
    FILTER(
        ALL('DDT'[Date (DDT)]),
        YEAR('DDT'[Date (DDT)]) = YEAR(MAX('DDT'[Date (DDT)])) - 1 &&
        'DDT'[Date (DDT)] <= DATE(
            YEAR(MAX('DDT'[Date (DDT)])) - 1, 
            MONTH(MAX('DDT'[Date (DDT)])), 
            DAY(MAX('DDT'[Date (DDT)]))
        )
    )
)

How this works:

• MAX('DDT'[Date (DDT)]) gets the current date from your visual's filter context
• For each month in your visual, it calculates YTD for that same month in the previous year
• Example: If looking at March 2025, it shows Jan-Mar 2024

To use in a visual:

• Create a line/column chart
• X-axis: Month (from your date table)
• Y-axis: Your new Previous YTD measure
• Each month will show the cumulative total up to that month in the prior year

This replaces the static TODAY() with dynamic date context from your visual.

Best regards!

PS: If you find this post helpful consider leaving kudos or mark it as solution