Forum Discussion
Dynamically calculate previous year based on slicer
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.
SAMEPERIODLASTYEAR is notoriously bad with custom fiscal calendars. Better use an external reference table that has the required columns precomputed.
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.
5 Replies
- lbendlin
Super User
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?
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.
- lbendlin
SAMEPERIODLASTYEAR is notoriously bad with custom fiscal calendars. Better use an external reference table that has the required columns precomputed.
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 - YouTubeYou 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.
