Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
Proud to be a Super User!
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 🙂
Hope this helps mate!
If I have posted a response that resolves your question, please accept it as a solution to formally close the post.
Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!
Want to connect?www.linkedin.com/in/theoconias
Thanks for your suggestion - I'm afraid I worked from the bottom up and the other reply fixed my issue 🙂
@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.
If I have posted a response that resolves your question, please accept it as a solution to formally close the post.
Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!
Want to connect?www.linkedin.com/in/theoconias
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
Proud to be a Super User!
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
82 | |
67 | |
61 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |