Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Any help here will be greatly appreciated.
I have a list of invoices with a posting date, from date and a to date, as well as the total amount.
No | Posting Date | From | To | Total | Desired Result for Sep-18 (example) |
1 | 01/09/2018 | 01/09/2018 | 30/11/2018 | 1200 | 400 |
2 | 15/06/2018 | 15/06/2018 | 15/09/2018 | 500 | 83.33 |
3 | 06/09/2018 | 25 | 25 | ||
4 | 09/09/2018 | 01/07/2018 | 01/07/2019 | 1200 | 300 |
5 | 28/09/2018 | 01/10/2018 | 31/10/2018 | 50 | 0 |
6 | 03/09/2017 | 03/09/2017 | 03/09/2018 | 1200 | 9.86 |
I would like to be able to date apportion these invoices based on a combination of the three dates above and be able to report total apportioned revenue for any given month.
What makes it really complicated to do is that there are multiple different scenarios where a slightly different calcuation needs to be applied. Standard logic should be that any given invoice is to be day apportioned based on from and to dates. However what makes it complicated is that other scenarios are possible i.e.
Is it possible to achieve this in PowerBi?
Thanks!
hi,arij66
After my research, I'm afraid it is complicated, i have done a formula like below,
and the days360 rule is complicated for this :
Column = IF(Table1[From]=BLANK(),Table1[Total], IF ( Table1[From] < Table1[Sep] && Table1[To] >= Table1[Sep], IF ( OR ( Table1[Posting Date] <= Table1[Sep], MONTH ( Table1[Posting Date] ) = MONTH ( Table1[From] ) ), Table1[Total] / IF ( YEAR ( Table1[From] ) = YEAR ( Table1[To] ) && MONTH ( Table1[From] ) = MONTH ( Table1[To] ), ( DATEDIFF ( Table1[From], Table1[To], MONTH ) ) + 1, DATEDIFF ( Table1[From], Table1[To], MONTH ) ) / 30 * ( DATEDIFF ( Table1[Sep], Table1[To], DAY ) + 1 ), ( Table1[Total] / DATEDIFF ( Table1[From], Table1[TO], MONTH ) ) * ( DATEDIFF ( Table1[From], Table1[Sep], MONTH ) + 1 ) ) ,IF(YEAR(Table1[From])=2018&&MONTH(Table1[From])=9,Table1[Total]/ ( DATEDIFF(Table1[From],Table1[To],DAY)/30),IF(DATEDIFF(Table1[Sep],Table1[From],DAY)>=30,0 ) )))
and here is pbix, please try it.
https://www.dropbox.com/s/yeijlrsxn2jfjyh/Spread%20amount%20based%20on%20THREE%20dates.pbix?dl=0
Best Regards,
Lin
Hi @arij66,
Can you please elaborate on how you are calculating the Desired result column, not really sure what you mean by:
Can you show what are you making the divisions and multiplications in order to get the 300 on row 4?
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsUser | Count |
---|---|
128 | |
108 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |