Balance due calculation -Its formula involves its cumulative previous months values

[Shanmathee]

Hi all

I am confused  in implementing a below explained  logic using dax.

please find below the sample data screenshot

Data Explanation

1. Data is  in fiscal year format .Each year, each customer month wise balance due is found

Excel Columns Definition

A     - Customer Name

B     - Fiscal year

C     -Fiscal Month number

D    - Month

E     - Gross Revenue(Month wise)

F     -cumulative Gross Revenue

        (For customer  “Sri”  cumulative gross revenue  for the month Oct =1705929.36  ,           Nov=Oct+Nov=>1705929.36+1681233.58=3387162.94

G     - 10% of Cumulative Gross Revenue

H      - Minimum pay (Manually entered value)

I       - Cumulative previous month Balance Due

          Excel formula  for the row 2 is ,I2 =J1 ,I3=J1+J2, I4 =J1+J2+J3

Logic is -It should give us the  balance Due till previous month in a cumulative way.

J      - Balance Due

        Excel formula for the row 2 is , J2 =if(G2-H2-I2<0,0,G2-H2-I2)

I need to obtain “Cumulative previous month Balance Due” and “ Balance Due “ columns in power bi

Logic Needed 

Month	Cumulative previous month Balance Due	BALANCE DUE
Oct	-	200
Nov	200	400
Dec	600	10
Jan	610	100
Feb	710	40
Mar	740	60
Apr	800	20
May	820	30
Jun	850	100
Jul	950	50
Aug	1000	25
Sep	1025	25

For example

For a Customer,in Oct -> “balance Due” is 200 then “Cumulative previous month Balance Due”  is null(since Oct is the 1st month)

                            In  Nov -> “balance Due is  400 which is calculated based on three columns

if(G2-H2-I2<0,0,G2-H2-I2) in excel

If(10% - Minimum Pay - Cumulative previous month Balance Due(200 in this example for Nov month))<0,0<10% - Minimum Pay - Cumulative previous month Balance Due(200 in this example)

Based on the above logic we get balance due as 400 for month of Nov

Problem

In Power BI ,I am not aware of how to achieve this

To calculate” balance due “(J column) I need to know the “cumulative previous month Balance Due”(I Column) which is cumulative value of J column till previous month.

With same data ,I need to do calculations for calendar year also.

Same logics I need to implement for Calendar year also.

Please  let me know how it can be done.I have converted fisca year into calendar year using logics and found "10%","Minimum Pay" columns for calendar year based calculation (eg -Fiscal year 2020 jan-sep & Fiscal year 2021 oct-dec is 2020 calendar year).But in both fiscal and calendar ,I am struggling to find a way to implement balance due.

I would be happy to hear solutions from you.Thanks in Advance

[V-pazhen-msft]

@Shanmathee 

Due to the policy of my team, we don't support any PM or email or any other communication tools. You could just upload to a cloud folder and share the link.

Paul

[V-pazhen-msft]

@Shanmathee 

If you don't mind, please a share a sample excel, this is not something can answered in text. I would need to do some test with the model.

Best 

Paul

[Shanmathee]

Hi @V-pazhen-msft 

Thanks for your response!

Could you please send me your mail id.so that I could send the sample file to you.i am unable to upload it here.