I am in need of some help to get me going. I have 4 fact tables - Forecast, Plan, Sales and Backlog.
I need to create some visuals that show how the sales are trending each week within the month, quarter or year.
Some of the trends I am tracking are as follows:
- Open orders + shipments (billings/ backlog) are "##" % of plan / fc within any filtered month/quarter/year.
-- and then same as above, but by product.
--- then rsm/region/territory.
This is done by rev, units and avg sales price.
I have the sales and plan totals working well. but am stumped on the open orders piece. The date table is currently linked to the "backlog as of date" as this is a table with our backlog snapshots and works well from that perspective. However, Open Orders for future months is based on another field named "Commit Date".
Problem 1: I don't know how to filter only on what has been committed for the outer months for a given time period.
Problem 2: I know how to do act/vs plan %s, but am not sure how to simulate future months act/plan or fc/plan
The magic keywords you are looking for are "DAX Events in Progress". There has been amazing work by @marcorusso ... well, and many other people... on the performance implications of various techniques. daxpatterns.com will likely give you a pattern to use if you don't care about the super perf geekery 🙂