Calculate Opening, Movement and Closing Balances

[Saes]

Hello all,

I've got some repairs data and I'm trying to calculate the following for each month of the year, but every method I have tried is not returning the expected results:

• Number of jobs open at start of the month
• Number of jobs logged in-month
• Number of jobs closed in-month
• Closing balance at the end of the month

The data set looks like this:

Job ID	Date Logged	Completion Date
JOB00001	12-Oct-23	03-Jan-24
JOB00002	14-Nov-23	NULL
JOB00003	12-Dec-23	23-Dec-23
JOB00004	15-Jan-24	20-Jan-24
JOB00005	16-Jan-24	NULL
JOB00006	20-Jan-24	NULL
etc.	etc.	etc.

And we would like to present it like this:

	Open Jobs at Start of Month	Jobs Logged In-Month	No of Jobs Complete In-Month	C/B
April	5,407	8,676	(8,760)	5,323
May	5,323	7,989	(8,325)	4,987
June	4,987	7,896	(8,464)	4,422

Can anyone help?

[v-shex-msft · Accepted solution]

HI @Saes,

According to your description, it seems like a common date range analysis requirement, you can take a look at the following blog (start/end date part) which provided some common scenarios if it helps:

Before You Post, Read This: start, end date 

In addition, you can also try to create a expand table to expand the detail date range of records that mapping with raw table row date ranges.

Solved: Spread revenue across period based on start and en... - Microsoft Fabric Community
Regards,

Xiaoxin Sheng