Revenue Recognition Automation

ChavdarG · ‎04-24-2021

Dear all,

I have hit a wall with this one and need help from the community.

Example file here: https://drive.google.com/file/d/17mOH9IoM2y_l89wcaN04KdSED5wLo695/view?usp=sharing

The idea is generally simple: once you ship a certain quantity of goods, not all are immediately recognized as sales. Some are in transit for the current month and some are recognized in the current month (which were in transit in previous months).

What is needed is that based on the available fact and dimension tables to:

- where I am stuck: get the final quantity sold per period (shipped - in transit + recognized as sales)

- next step if possible: get the final revenue per period. Revenue of shipped - revenue in transit (based on the average per unit x quantity in transit) + revenue recognized (based on the average per unit x quantity recognized)

Why am I stuck on step 1:

-I got to a point where I get a certain quantity but since it is based on the fact table data, if there are sales in transit from previous periods (such as CC 62037, which was in transit end 2019 and sold in Jan 2020), it does not appear in my list

- the solution I see is to add to my formula for Final_Volumes also the volumes of CCs not in the fact table list but in the SiT list

- However, both lists contain duplicate values and I cannot do it

The end result for January in this example is Final Volumes of 404,742:

I'd also accept recommendation of different data modelling, if more optimal.

Let me know if further clarifications are needed.

Thank you,

Chavdar

v-kelly-msft · ‎04-28-2021

Hi @ChavdarG ,

I cant match your data with your requirement,could you pls check again and advise what is your expected output based on your sample data?

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

ChavdarG · ‎05-03-2021

Hello,

The pivot table in the sheet displays Final Volumes, the number of which is currently 389,742 because there is a cost center missing (62037). Should be 404,742 including 62037. It is missing because Final Volumes is a calculated column based on the data of the FactTable. However, the sales of 62037 come from the Sales table. They are not in the FactTable because these are shipments from previous periods recognized as sales in the current period.

The first thing I need to do is add any quantities recognized as sales from the Sales table, that are missing in the FactTable, to the Final Volumes formula.

I guess may be not that straightforward or I cannot explain it well. Does it make sense now?

Thanks,

Chavdar