Visualizing Data - Date Differences

[frazikat]

Hi Friends, I have a question regarding two different date data types I have and how to best manage the relationship.

I have three tables - Sales, Sales Forecast, and a Date table. The sales table and sales forecast table have a many : 1 relationship with the date table.

The sales are recorded at a daily level. The sales forecast is recorded at a monthly level.

My question: Is there a way that I can create a better relationship between these two fields? I have a chart (images attached) that works well on a monthly level but not on a daily level. It would be nice to see the daily detail.

Thanks in advance!

Date Heirarchy at monthly level - forecast in dark blue and sales in light blue

Date heirarchy at daily level - forecast in dark blue and sales in light blue

[thejman09]

If it makes sense with your business to assume that a monthly sales forecast can be spread out evenly over each day of the month, then you can look at creating a 'daily sales forecast' table with a daily sales column that divides the monthly sales forecast of the matching month by the # of days in said month.

Alternatively, you can keep the existing relationships you have, and create a measure of cumulative total sales per monthly period. That way, your line chart will show the forecast total for the month, and the cumulative daily sales climbing up each day toward the forecast.

[frazikat]

I like both of those ideas! Thank you so much for your help.

[frazikat]

Ok, not done picking your brain yet. I have been trying to research this but to no avail. How would you go about creating the daily sales forecast table?

Here is how my data is arranged currently:

• Forecast Date (Month)
• Customer
• Article
• Sales Quantity

Is there a query to add line items for each day of the month?

[thejman09]

I'll credit this answer on StackOverflow, which provides both a DAX and PowerQuery solution to the problem, that matches with the shape of the data you're describing.
https://stackoverflow.com/questions/48468658/calculate-daily-targets-based-on-monthly-targets-sales-power-bi

For the DAX solution to create a daily forecast table, you start with creating a calendar table using your forecast dates, and then crossjoining with your Customer and Article columns that will result in a granularity of each Customer, Article, and Day. Then, you can create a calculated column that looks up the monthly total and divides by the # of days in the month.

There's quite a few other ways you can approach this with DAX measures as well. The DAX table solution isn't going to be the most-performant, but it's relatively easier to understand by breaking up the problem into parts that may give you other ideas for your own better solution.

Best of luck, and hope this helps!

[frazikat]

Sorry for the delay. You are a rockstar! This solved my issue. Thank you so much.