I've created a calculated column in M. The idea is that the earned revenue is calculated as follows: It's for a subscription model. Amount is paid up front but is "earned" over the year.
On the contract date, the amount earned is equal to the Amount * 1- Deferred % e.g. if the deferred % is 20%, the amount earned that day will be 80% of the amount plus the balance (20% of the Amount) divided by the number of revenue days i.e. the number of days between the start date and the end date. Otherwise, on all other days of the subscription, the amount earned will be equal to Amount * Deferred% divided by the revenue days. However when I do this calculation and pivot it. I get different results. I have put the desired and actual results in the attached file. I've highlighted in yellow what I am getting and what I should be getting.
here's the M code - can () be put on various bits of it?
Text of calculated column
if [#"Deferred %"]=1 or [#"Deferred %"]=0 or [In Contract Date]="No" then 0 else if [#"S&M Contract Start date"]=[Revenue Date] then [Amount]*1-[#"Deferred %"] + [Amount]* [#"Deferred %"]/[Revenue Days] else [Amount]*[#"Deferred %"]/[Revenue Days]
Thank you as always...
Hi Anne,
Yes, you can add () to your Power Query formula to force the order of calculation, but it's not necessary for the 'if' itself, as you know.
I'm not sure where the "PivotTable" is that you refer to in your file and there's no query or yellow highlight. I'm having trouble seeing how you got the incorrect result without seeing the workings.
Mynda
That's the bit I am missing - the () . All I wanted was to review the formula...will try that now! Thank you..
