cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sdmikejr Regular Visitor
Regular Visitor

Help with future months calculations by week, month or quarter

Hello, 

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

 

Any thoughts?

1 REPLY 1
scottsen Senior Member
Senior Member

Re: Help with future months calculations by week, month or quarter

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 🙂

 

 

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)