cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
arij66
Frequent Visitor

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. 

 

NoPosting DateFromToTotalDesired Result for Sep-18 (example)
101/09/201801/09/201830/11/20181200400
215/06/201815/06/201815/09/201850083.33
306/09/2018  2525
409/09/201801/07/201801/07/20191200300
528/09/201801/10/201831/10/2018500
603/09/201703/09/201703/09/201812009.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.

 

  • Invoice posted in September 18, but with a historic from date (invoice 4 above). In this case, all historic portion of the invoice should be absorbed on the day of the invoice, with the remainder of the amount apportioned to future dates at a standard daily rate
  • Invoice posted in September 18, but with no to or from date (invoice 3 above). In this case, the total amount should be entirely apportioned to the posting date. 

Is it possible to achieve this in PowerBi?

 

Thanks!

3 REPLIES 3
Super User III
Super User III

Re: Spread amount based on THREE dates

Hi @arij66,

 

Can you please elaborate on how you are calculating the Desired result column, not really sure what you mean by:

 

  • Invoice posted in September 18, but with a historic from date (invoice 4 above). In this case, all historic portion of the invoice should be absorbed on the day of the invoice, with the remainder of the amount apportioned to future dates at a standard daily rate

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


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Check out my blog: Power BI em Português


arij66
Frequent Visitor

Re: Spread amount based on THREE dates

Hi ,

In some rare cases what can happen is a customer is invoiced late, so a customer was should was to be invoiced on 1st July 2018, was instead invoiced in September. If that happens the accounting policy is that the historic period of the invoice should be recognised straight away, in this case in September.

So in this instance the invoice is for 1200 for the period of 1st July 18 to 1st July 19. Therefore revenue of 100 would be allocated to each month from July to June next year. The invoice was issued late so the revenue of 100 from July 18, and 100 from Aug 18 would be recognised together along with September's proportion.

Hope this makes sense. What I should also add is that I am applying the days360 rule when apportioning the invoices to avoid fluctuations in apportionment due varying number of days between different months.

Many thanks
Highlighted
Community Support
Community Support

Re: Spread amount based on THREE dates

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
            
)
)))

10.PNG

 

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

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors