cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mattgrimm
Frequent Visitor

SamePeriodLastYear with a twist

Hoping the experts here can help me with this situation.

 

I have a detail dataset where each row represents a executed function:

  • Each record has a start date and end date
  • each recor as a "category" that I'm running the metrics for

My initial goal was to show the YOY % change between turn times. I successfully did this by creating (7) measures to handle it:

  1. SUM of the turn times
  2. Count of the turn times
  3. Sum of the turn times using SAMERPERIODLASTYEAR
  4. Count of turn times using SAMERPERIODLASTYEAR
  5. AVG TT using the sum/count in 1 and 2
  6. AVG TT using the sum/count in 3 & 4
  7. % change calculation of 5 and 6

I was successful:

CalculationExample_1.png

 

I have a year over year view of turn times and the % change. However, what I want to do as an extension doesn't seem to really work here and I'm not sure if there's a better expression or approach to this.

 

I want to calculate the % change across years. EX - I want to isolate 2019 and 2022 to show the % change between those years. So 76.35 vs 22.75. When I filter to those years though, through a slicer, it still reains the same %'s (since the data is still there). 

 

CalculationExample_2.png

When I filter the dataset from the filter window to ONLY 2019 and 2022 then the DAX expression breaks.

 

Is there a DAX expression that calculates year to year volume based on the years present and/or staggered INSTEAD of the sameperiodlastyear which is looking for the exact previous year?

 

Thanks all in advance for your help.

1 REPLY 1
speedramps
Super User
Super User

Only Power BI novices use SamePeriodLastYear and other DAX inbult date function.

 

If you want to become Power BI the start using cvalendar tables for ALL date functions.

Click here to start learning about calendar tables 

Then click here to learn about calendar offsets 

 

Thanks for reaching out for help.

I put in a lot of effort to help you, now please quickly help me by giving kudos.

Remeber we are unpaid volunteers and here to coach you with Power BI and DAX skills and techniques, not do the users job for them. So please click the thumbs up and accept as solution button. 

If you give someone a fish then you only give them one meal, but if you teach them how to fish then they can feed themselves and teach others for a lifetime.  I prefer to teach members on this forum techniques rather give full solutions and do their job. You can then adapt the technique for your solution, learn some DAX skills for next time and soon become a Power BI Super User like me. 

 

One question per ticket please. If you need to extend your request then please raise a new ticket.

You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you ! 

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power BI Dev Camp Session 27

Ted's Dev Camp

This session walks through creating a new Azure AD B2C tenant and configuring it with user flows and custom policies.