Forum Discussion

mfinlay's avatar
mfinlay
Advocate II
4 years ago
Solved

Prior year calculations based on two dates

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

  • 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

5 Replies

  • richbenmintz's avatar
    richbenmintz
    Resident Rockstar

    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's avatar
      mfinlay
      Advocate II

      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.

  • TheoC's avatar
    TheoC
    Community Champion

    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's avatar
      mfinlay
      Advocate II

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

      • TheoC's avatar
        TheoC
        Community Champion

        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.