Forum Discussion

mfinlay's avatar
mfinlay
Icon for Advocate II rankAdvocate 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
    Icon for Resident Rockstar rankResident 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
      Icon for Advocate II rankAdvocate 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
    Icon for Community Champion rankCommunity 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
      Icon for Advocate II rankAdvocate 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
        Icon for Community Champion rankCommunity 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.