Forum Discussion

VAL_94's avatar
VAL_94
New Member
2 years ago

Cap(limit) first n orders

I am doing a power bi to track money spent on promotions on a whole variety of stores, but I am experiencing an issue on the cap (limit of units with promotion). Every promotion has a limit of units assigned to each store so that there is not over expenditures.
For instance one store on a troussers promotion can give promotion to first 6 purchases and another shirt promotion may be cap to 10 first items.

My model has the following tables:

1) [Store, Promotion, Cap]: It registers all promotions available on all possible stores and the quantity limit for each pair
2) [Date, Store, Promotion, Quantity, Price, Discount]: I get the sales and "invested money on promotions" by promo and store by doing the following measure: Investment = quantity * price * discount.

With a TOPN formula I have been able to get the investment and also the cap_investment (which is the previous formula taking into account the mentioned cap). HOWEVER when I try to get the same metrics on a time graph, the TOPN formula evaluates, not per store & promo, BUT on a DAILY BASIS and the cap is never reached on a daily basis.

I would expect to see the cap_investment to be equal to the investment on a daily trend until the cap is reached and the cap_investment then turns 0 but it is not being the case since the TOPN formula evaluates on a daily basis.

I hope I made a clear explanation.
Happy to expand if needed.
Thanks!!

 

1 Reply