Forum Discussion
Helper ICalculate Opening, Movement and Closing Balances
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?
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
1 Reply
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