Calculation of Age (Credit days) flag based on last 6 months sales and Ledger Closing Balance

[vkboddapati]

Hello Geeks,
This was done in Excel, Kindly help to calculate and replicate this in Power BI.
I have a list of 'Area', 'Sub-Area', 'Distributor', 'Sub-Distributor', and 'Customer Name' fields against which I have month-wise sales figures (columns D to I) and 'Closing Ledger Balance' (Column J).

I want to compute the 'Credit Days' based on 'Closing Ledger Balance' (Column J) and sales figures (columns D to I). In excel the formula is straight forward as below: 
=IF(J3<=1000,0,IF(J3<=I3+100,J3/I3*30,IF(J3<=I3+H3+100,30+(J3-I3)/H3*30,IF(J3<=I3+H3+G3+100,60+(J3-I3-H3)/G3*30,IF(J3<=I3+H3+G3+F3+100,90+(J3-I3-H3-G3)/F3*30,IF(J3<=I3+H3+G3+F3+E3+100,120+(J3-I3-H3-G3-F3)/E3*30,IF(J3<=I3+H3+G3+F3+E3+D3+100,150+(J3-I3-H3-G3-F3-E3)/D3*30,"Above 180 days")))))))

Based on computed 'Credit Days', I woluld distribute it into respective ageing bracket (as shown in the pic/file).

Below is the link to excel file,Kindly help on how to replicate this in to power BI.

https://docs.google.com/spreadsheets/d/1T5AftzkiRCzD2NcZvZMqWTcgsk9nCS-t/edit?usp=sharing&ouid=10908... 

Cheers!

VKB

[vkboddapati]

Thanks, @lbendlin @Ashish_Mathur for your replies and understanding.
I am desperately trying to do this in Power BI. 
Kindly suggest if there are any options to make changes at the source level too.

Thanks again! adding back @Greg_Deckler @v-shex-msft too

[lbendlin]

Keep doing this in Excel.

[vkboddapati]

😂Not expected that in the community! @lbendlin 

Copying the Masters of the Game 👇

@Greg_Deckler @v-shex-msft  @Ashish_Mathur @amitchandak 

[Ashish_Mathur]

Hi,

Even in MS Excel, this is not an easy calculation - let alone doing it in PBI Desktop.  I had blogged about my solution to this problem 12 years ago.  See this article - Compute Debtor days.