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 there,
I'm trying to compare sales per day across the various forecast versions. I have a sales table, date table, and a version table. See below and I've attached the file as well. I was thinking I can create a relationship between my date table and my version table, but that is a many to many relationship which I believe is not best practice. Appreciate the help!
This is what I trying to get:
Q1 2020 sales per day = 35,979,184 / 91 days = 395,375 $/day
Q1 2021 budget sales per day = 37,243,371 / 90 days = 413,815 $/day
Q1 2021 2+10 Forecast sales per day = 35,914,112 / 90 days = 399,045 $/day
But this is what I get:
Q1 2020 sales per day = 99,665
Q1 2021 budget sales per day = 103,167
Q1 2021 2+10 Forecast sales per day = 99,485
https://drive.google.com/file/d/1SPqIb2ZQVXo0gkBAEX7ZWiNrszi823rF/view?usp=sharing
Solved! Go to Solution.
Hi, @curiouscookie
I am not sure if I understood your question correctly. But please try to amend one of your measures to the below.
Days in Period =
CALCULATE (
DISTINCTCOUNT ( Fact_Date[Date] ),
FILTER ( Fact_Date, INT ( Fact_Date[Year] ) = MAX ( Tbl_Sales[Year] ) )
)
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Linkedin: linkedin.com/in/jihwankim1975/
Twitter: twitter.com/Jihwan_JHKIM
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
@curiouscookie It looks like you're trying to compare 2020 actuals to 2021 budget? Is that correct? You may have luck with using time intelligence DATEADD function to shift the date context of budget or actual by 1 year or -1 year depending on which one you shift.
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
Hi Allison, thank you for your response, unfortunately that won't work because I could also be comparing to different versions within the same year since we do monthly forecasting. But good tip for the future!
Hi, @curiouscookie
I am not sure if I understood your question correctly. But please try to amend one of your measures to the below.
Days in Period =
CALCULATE (
DISTINCTCOUNT ( Fact_Date[Date] ),
FILTER ( Fact_Date, INT ( Fact_Date[Year] ) = MAX ( Tbl_Sales[Year] ) )
)
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Linkedin: linkedin.com/in/jihwankim1975/
Twitter: twitter.com/Jihwan_JHKIM
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Thank you for your help! This is exactly what I was looking for I couldn't understand why the two tables didn't produce the same results.
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 |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |