topic Difference between monthly forecast and daily sales in DAX Commands and Tips

Difference between monthly forecast and daily sales

Anonymous — Tue, 28 Apr 2020 12:42:03 GMT

I have 2 tables. One table containing information about my sales (on a daily basis) and one table containing forecast (on a monthly basis). My end goal is to be able to easliy create a top 5 chart for a certain product or certain customer and time-period (could be one month or more) where sales > forecast or sales < forecast.

For this I assume I will need to make a new table and I've created an example in excel of what I need, however I do not know how to apply this in power BI (I assume to create a new table using DAX, but I don't know where to begin).

Picture of my goal

Re: Difference between monthly forecast and daily sales

Mariusz — Tue, 28 Apr 2020 16:13:12 GMT

Hi Anonymous

Try these articles

https://www.daxpatterns.com/budget-patterns/
https://www.daxpatterns.com/handling-different-granularities/

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

Re: Difference between monthly forecast and daily sales

Anonymous — Tue, 28 Apr 2020 20:04:06 GMT

Solution attached. Always make sure the product table contains all products in Sales and Forecast and that the Customer dimension contains all customers from Sales and Forecast as well. The Time dimension must be a proper Date table covering all the years found in the fact tables in full.

Best