Good day,
spent a lot of time looking for a solution, but no luck
I have a table with the following data
Client Date_begin Date_end Termination_date Sales$
AAA 01.06.2021 31.05.2022 500
BBB 01.01.2022 31.12.2022 400
BBB 30.04.2022 -100
CCC 03.05.2022 02.08.2022 100
(today()-sum(Table[date_end]))/(in sales$ column negative sales mean refund, in case refund made all sales are considered earned on an accrual basis)
What i am trying to do
1. add a column (accual_coeff) with the following formula
if there exists data in termination_date, 1, otherwise
(sum(Table[date_end])-sum(Table[date_begin]))
2. Add a measure that is the multiplication of accrual_coeff and Sales$
As a result i'd have
Client Sales$ accural coeff Accued Profit
AAA 500 0.97 483.5
BBB 300 1.00 300.0
CCC 100 0.18 17.6
I can't do it in power query by grouping, since this table is a small part of a big table, and i am going to use with multiple other combinations
Any help would be greatly appreciated
Hi @Bekzat ,
Sorry, not very clear.
I have a few questions for you, the accual_coeff of AAA is 0.97, how did you get it? Is there a specific formula to provide?
Table[date_end] is a date, how to sum up?
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
108 | |
63 | |
60 | |
37 | |
37 |
User | Count |
---|---|
110 | |
67 | |
62 | |
60 | |
50 |