Need to calculate opening balance for below data

[Anonymous]

I need to calculate opening balance for below data, please help

https://docs.google.com/spreadsheets/d/1Z-g2Zl9vqWQtCiT-fItjOx0YASMVZqF7/edit?usp=sharing&ouid=11403... 

DATA
CREATED DATE	RELEASED DATE	COMPLETION DATE	QUANTITY
01-01-2021			15
02-01-2021	02-01-2021	02-01-2021	10
03-01-2021	04-01-2021	06-01-2021	20
04-01-2021	06-01-2021		40
05-01-2021	05-01-2021	06-01-2021	50
06-01-2021	06-01-2021		60
OUTPUT				CONDITION
CREATED  DATE	OPENING BAL			OPENING BAL = RELEASED DATE <  REPORT DATE
01-01-2021	0			AND
02-01-2021	0			COMPLETION DATE > REPORT DATE
03-01-2021	0
04-01-2021	0
05-01-2021	20
06-01-2021	70
07-01-2021	100

[Anonymous]

Hi @Anonymous ,

I still have some doubt. Where is the report date?  The data sample seems to be  missing date. Please check. And how to calculate to get 100?

Best Regards

Community Support Team _ Polly

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

[Fowmy]

@Anonymous 

Few questions, where is the Release Date in your model? Can you show how the calculation for the Opening balance? The condition you mentioned isn't enough to understand the logic. You make do in Excel, save in OneDrive or any other location and share the link here.

[Anonymous]

@Fowmy  creation date, released and completion dates are available in fact table. Also created date is linked with calendar table in model.

https://docs.google.com/spreadsheets/d/1Z-g2Zl9vqWQtCiT-fItjOx0YASMVZqF7/edit?usp=sharing&ouid=11403...