Prior year calculations based on two dates

[mfinlay]

Hi Power BI gurus,

I have a measure that has two dates associated with it - a booking date and a shipping date.

I can get prior year calculations easily for one date, using calculate and dateadd DAX, eg for shipping date:

However, I also want to apply the same filter/calculation to the booking date - ie to effectively see how things are performing at the same point as last year.

 Adding a filter on booking date to the table removes the PY numbers, as the PY shipping dates don't have a booking date in the current year:

Can anyone suggest how I can get the booking date to also go back 364 days, eg to show 434 for Dec-21 in the Transaction Count PY column - which is the total of bookings taken 02/09/2020 - 22/09/2020 with shipping dates 02/12/2020 - 01/01/2021?

I've tried applying two filters to the CALCULATE function:

Transaction count Prior Year - Booking and Shipping Date = 
CALCULATE (
    [Transaction count],
    DATEADD ( 'Shipping Date'[Shipping Date], -364, DAY ),
    DATEADD ( 'Booking Date'[Booking Date], -364, DAY )
)

but this throws an error:

I'm not looking for a complete solution, I'm happy to try and resolve myself if someone could please give me a pointer.

PBIX file:

https://1drv.ms/u/s!AnNbCRaQC4GBhykaMux22jk4tAy5?e=PS0xjx

thanks

Matt

[TheoC]

Hi @mfinlay 

Create a new measure in your Data table as below. This will ignore any filters from both tables and hopefully give you what you're looking for 🙂

Transaction Count (Total CY) =

CALCULATE ( TOTALYTD ( [Transaction count] , Data[Shipping Date] ) , ALL ( 'Booking Date'[Booking Date] ) , ALL ( Data[Booking Date] ) )

Hope this helps mate!

[mfinlay]

Thanks for your suggestion - I'm afraid I worked from the bottom up and the other reply fixed my issue 🙂

[TheoC]

@mfinlay LOL! Mate, too good. Best thing about Power BI is that there's a lot of ways to achieve the outcome you're after.  And I am pretty sure the @richbenmintz approach was much quicker.

[richbenmintz · Accepted solution]

Hi @mfinlay ,

You can fix your error, by changing your relationship between booking date and data to 1 -> Many and changing to one way.

Will review the formula next to try to get the correct result

[mfinlay]

Ah, hadn't spotted that the test data had caused an incorrect join.

That has fixed my calculation, and the calculation appears to work as I'd hoped - thank you.