Forum Discussion

arij66's avatar
arij66
Frequent Visitor
7 years ago

Spread amount based on THREE dates

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 ...
v-lili6-msft's avatar
v-lili6-msft
Community Support
7 years ago

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