Need help to accomplish on this calculation. Have been trying for last few days unsuccessfully

[Anonymous]

 

Hi Folks

I have 3 tables

1st - Open Cases table

2nd - Closed Cases Table

3rd - Backlog table with dates running down from 14th April 2020 to 31st December 2020

When I start this calculation in backlog table, by default initiate the counter with static value of 20

on the subsequent days, I do the following calculation to get the values for each respective dates in the backlog table

For Example:

13th April 2020 - ((Previous day backlog counter + Total # of new Cases on 13th April 2020) - Closed cases on 13th April 2020)

I do this calculation for all the days from 13th - so, i will keep storing - backlog data.

I have done this in excel. need help to do this Powerbi. 

[amitchandak]

@Anonymous , I would suggest copy close date from close table to open table like this  in a new column

close date = maxx(filter(closecase, closecase[casenumber] = opencase[casenumber]),closecase[closedate])

Now refer this file or my HR blog to check for a current employee in  HR

open case  (Measure in file and backlog in your case)

https://www.dropbox.com/s/97ff7141d5atppy/daysbetweendates.pbix?dl=0

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

https://www.dropbox.com/s/excoqikgpyz5zvk/Current_employee.pbix?dl=0

[Anonymous]

Thanks @amitchandak for your prompt response. I shall try this out later in the day and will post the feedback. 

[v-yiruan-msft · Accepted solution]

Hi @Anonymous ,

You can create a measure as below:

Total backlog for the day = 
IF (
    MAX ( 'Backlog Count'[Date] ) = DATE ( 2020, 4, 12 ),
    20,
    20
        + CALCULATE (
            COUNT ( 'Open Cases'[Case Number] ),
            FILTER (
                'Open Cases',
                'Open Cases'[Date] <= MAX ( 'Backlog Count'[Date] )
                    && 'Open Cases'[Date] > DATE ( 2020, 4, 12 )
            )
        )
        - CALCULATE (
            COUNT ( 'Closed Cases'[Case Number] ),
            FILTER (
                'Closed Cases',
                'Closed Cases'[Date] <= MAX ( 'Backlog Count'[Date] )
                    && 'Closed Cases'[Date] > DATE ( 2020, 4, 12 )
            )
        )
)

Best Regards

Rena

[Anonymous]

@v-yiruan-msft - I tried this and was easily able to implement. Thanks for your Support.

I think there is some logic problem while calculating

For Example - Closes Casesd on

13th - 62,

14th - 171 (but actually it is adding 13th and 14th together)

15th - 277 (it is adding 13th, 14th and 15th values)

- the same is happening for open cases as well. Hence, some minor correction is required in the calculations. Please help. 

In order to identify the root cause, I had split closed cases, open cases measures separately. Please see the snapshot below

ScriptsData output 

[v-yiruan-msft]

Hi @Anonymous ,

The measure "total backlog for the day" is only for calculating the count of backlog for the day, whether that value is correct or not when applied in your scenario? And you can use the following measures to calculate the count of total count of open cases and closed cases in some day, they will only calculate the count of open cases and closed cases on the day.

closed cases =
CALCULATE (
COUNT ( 'Powerbi_ClosedCases3 (2)'[Standard_Flag] ),
FILTER (
'Powerbi_ClosedCases3 (2)',
'Powerbi_ClosedCases3 (2)'[Date] = MAX ( 'Backlog_Tracker'[Date/Time] )
)
)

open cases =
CALCULATE (
COUNT ( 'Powerbi_Opencases'[backlog_Counter] ),
FILTER (
'Powerbi_Opencases',
'Powerbi_Opencases'[Date] = MAX ( 'Backlog_Tracker'[Date/Time] )
)
)

Best Regards

Rena

[Anonymous]

@v-yiruan-msft . Thank you for your response. Getting the count of open and closes were not a challenge. The challenge noted through this query is how do we insert the daily count into respective dates on reoccurent basis. 

[v-yiruan-msft]

Hi @Anonymous ，

Thank you for your feedback. I am a little confused now, what is the final result you want? If you are counting the total backlog of each day, the measure “Total backlog for the day” I provided in my first post should return the correct value. The daily backlog is equal to the initial backlog plus the total difference between the cumulative number of open and closed cases. Did I misunderstand your needs?

Best Regards

Rena

[Anonymous]

@v-yiruan-msft - Thank you for your prompt support

I recall the original message once more and elaborate it bit more in the end

I have 3 tables

1st - Open Cases table

2nd - Closed Cases Table

3rd - Backlog table with dates running down from 14th April 2020 to 31st December 2020

When I start this calculation in backlog table, by default initiate the counter with static value of 20

on the subsequent days, I do the following calculation to get the values for each respective dates in the backlog table

For Example:

13th April 2020 - ((Previous day backlog counter + Total # of new Cases on 13th April 2020) - Closed cases on 13th April 2020)

14th April 2020 - ((Previous day backlog counter + Total # of new Cases on 14th April 2020) - Closed cases on 14th April 2020)

**Here what happens is 14th April, Open and Closed gets cumulative data of 13th and 14th values, so the backlog value is incorrect.

Thank you in advance!

[v-yiruan-msft]

Hi @Anonymous ,

If you are afraid the return value of measure "Total backlog in a day" are not correct, the easy method is calculate it in excel sheet first then compare the values in excel and the returned value of measure "Total backlog in a day" to validate if they are same...

Here I will explain more why I create the measre with that formula "original backlog+Cumulative difference of open cases and closed cases"... For example, 12th April 2020  backlog 943, then the total backlog of 14th April 2020 will be 

13th April 2020 backlog  =  943 + Total # of new Cases on 13th April 2020 - Closed cases on 13th April 2020

14th April 2020 backlog  =   13th April 2020 backlog + Total # of new Cases on 14th April 2020 - Closed cases on 14th April 2020

Then calculate the 14th April 2020 backlog also can use the following formula, it is equivalent to the previous formula:

14th April 2020 backlog   =  943 + Total # of new Cases on 13th April 2020 - Closed cases on 13th April 2020+ Total # of new Cases on 14th April 2020 - Closed cases on 14th April 2020

 As you can see the green font in the above formula, are they just an accumulated number of open cases and closed cases subtracted? 

Best Regards

Rena

[Anonymous]

@v-yiruan-msft . Thanks Rena for clarification - what you are saying makes sense. I shall do deeper analysis to see where the calculation is wrong in my report. I shall confirm back to you in a day or two.

Regards, Karan.

[v-yiruan-msft]

Hi @Anonymous ,

If the measure can return your desired result after you analyzed the data? If yes, could you please mark the helpful post as Answered? It will help other members in the community find the solution easily if they face the similar problem with you. Thank you.

Best Regards

Rena

[v-yiruan-msft]

Hi @Anonymous ,

OK. If there is something else that needs my help about this thread later, you can contact me. I'm looking forward to your feedback. 

Best Regards

Rena

[Anonymous]

Thank you @v-yiruan-msft . I appreciate your time and support. I shall try this and confirm you by tomorrow.