Dear Community,
I'm trying to build a chart showing service requests created, service request closed and active service request in a given time period. I also have a chart showing per month status (created - closed) but this is not showing the full picture as it shows only the difference. I'm being chased to provide cumulative line showing the actual work on hand.
I currently have the following tables and sample structure:
DATE
Date; Week Number; Day Of the Week
SERVICE REQUESTS
Service Request ID; Created Date; Closed Date
I have built some DAX formulas and visuals to show the backlog trend per month, ie. specific year/month/week what is the balance
Count Service Requests = DISTINCTCOUNT('SERVICE REQUESTS'[Service Request ID])
Count service request closed = CALCULATE(DISTINCTCOUNT('SERVICE REQUESTS'[Service Request ID]),USERELATIONSHIP('SERVICE REQUESTS'[CLOSED DATE 'Date'[Date]))
Service Request ballance = [Count Service Requests] - [count service request closed]
This is working quite good but obviously shows the balance as a relative number and not showing the total of active cases every month. I was trying to build a formula similar to what I was doing in excel - but I'm lost. I admit that I'm still not fully understanding DAX and the work with calendar /date
I was hoping that the following logic would work:
Service Request balance cumulative = [Count Service Requests] - [count service request closed] + <Result of the Service Request balance cumulative from the previous perriod>
Can you, please, direct me to a proper way of achieving that calculation?
Cheers!
Jiri
