Dynamically calculate previous year based on slicer

[GretchenRW]

I'm trying to create a matrix visual that dynamically changes the current year calculations based on the year selected in a slicer. My data set contains data from 2023-2025 and I need to calculate YTD Sales, YTD Qty, Prior YTD Sales and Prior YTD Qty with the current YTD changing along with the date selected within the slicer. I've been able to calculate YTD for this year and create a -1 calculation to determine the prior year, but that is only working if 2025 is selected. I know there is a way to do this, I just can't find the right tutorial. Any help you can provide would be greatly appreciated.

[lbendlin · Accepted solution]

SAMEPERIODLASTYEAR is notoriously bad with custom fiscal calendars.  Better use an external reference table that has the required columns precomputed.

[Anonymous]

Thank you lbendlin 

Hi, @GretchenRW 

You can learn how to calculate dynamic YTD in this tutorial below:

Week-related calculations – DAX Patterns

OR watch video:
How to Build Dynamic MTD YTD DAX calculations in Power Bi | Time intelligence functions | DAX - YouT...

You can also refer to the article on PYTD for practice:

I've uploaded a sample PBIX file below:

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

There's a lot of ways to do this.  Can you confirm that your data model has a Calendar table?  Have you considered using Visual Calculations?

[GretchenRW]

I have a fiscal calendar that breaks each day into the appropriate fiscal period. So far I have tried utilizing SAMEPERIODLASTYEAR but it is not returning the expected results.

[Anonymous · Accepted solution]

Hi, @GretchenRW 

Avoid using time intelligence functions on non-standard dates:

SAMEPERIODLASTYEAR – DAX Guide

You should install the example from the entire blog below, by doing the PY,YOY calculations without relying on the temporal intelligence function.

Week-related calculations – DAX Patterns

For standard time, you can use the Time Intelligence function as instructed in the blog below.

Standard time-related calculations – DAX Patterns

If your date table is more customizable, you can also customize the time calculation YOY:

Custom Year-Over-Year Calculation in DAX - SQLBI

Computing MTD, QTD, YTD in Power BI for the current period - SQLBI

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin · Accepted solution]

SAMEPERIODLASTYEAR is notoriously bad with custom fiscal calendars.  Better use an external reference table that has the required columns precomputed.