Sales per Day by Quarter (many to many?)

[curiouscookie]

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 

[AllisonKennedy]

@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.

[curiouscookie]

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!

[Jihwan_Kim · Accepted 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

[curiouscookie]

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.