how to create a formula that dynamically calculates the backlog count based on the selected level

[Sailu123]

I have data like this and i have one more data table. So i can easily calculated inbound and outbound 
But i want to calculate the backlog cound dynamically based on the select level.

i have cread a slicer  

when i selected 4th level i'm getting correct result by this formula 

But it is not working for 3rd level

[v-kaiyue-msft · Accepted solution]

Hi @Sailu123 ,

Create measure2 based on the previous measure.

Measure2 = 
VAR _date =
    CALCULATE (
        MAX ( 'Date Table'[Date] ),
        FILTER (
            'Incident_Tracker',
            'Incident_Tracker'[Incident Count] <> 0
                && 'Incident_Tracker'[Outbound] <> 0
        )
    )
VAR _pre =
    IF ( _date = BLANK (), MAX ( 'Date Table'[Date] ) - 1, _date )
RETURN
    CALCULATE ( 'Incident_Tracker'[Measure], 'Date Table'[Date] = _pre )

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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

[Sailu123]

Hi @v-kaiyue-msft ,

Thank you for your support. However, I'm encountering another challenge: when there are no inbound or outbound tasks continuously, the backlog count shows as 0. Can you please assist me here

[v-kaiyue-msft]

Hi @Sailu123 ,

It works when the 3rd level is selected, but it is possible that at this point there is only data for the 3rd of May, so the display is not obvious.

Therefore, the dax expression could be modified slightly to convert it to the number 0 when it returns null.

Measure = 
    VAR currentDate = MAX('Date Table'[Date])  
    VAR endOfDay =
        DATE(YEAR(currentDate), MONTH(currentDate), DAY(currentDate))
    VAR SelectedLevel = SELECTEDVALUE('Incident_Tracker'[Level])
    VAR f = FILTER(ALL(Incident_Tracker),Incident_Tracker[Assigned Date(DD-MM-YYYY)] <= endOfDay && 'Incident_Tracker'[Level] = SelectedLevel)
    VAR g = FILTER(ALL(Incident_Tracker), COALESCE(Incident_Tracker[Date Closed(DD-MM-YYYY)], endOfDay + 1) <= endOfDay && 'Incident_Tracker'[Level] = SelectedLevel)
    VAR result = COUNTROWS(f) - COUNTROWS(g)
    RETURN 
    IF(result = BLANK(),0,result)

Best Regards,

Clara Gong

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

[Sailu123]

Hi, Thanks for the support. But i want the result based on this criteria
-the backlog count should be the same even if there is no inbound and outbound 

for example: Required the output as shown in the below

[v-kaiyue-msft]

Hi @Sailu123 ,

Can you tell me on what basis the backlog counts are obtained when there is no inbound and outbound? A detailed explanation would help me to better help you out.

Best Regards,

Clara Gong

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

[Sailu123]

Hi... The backlog count will be the same as the previous date if there is no inbound and outbound.

for example:

Assueme..
on 29th we received 3 inbound and 2 outbound ..so we have 1 backlog ticket
on 30th we have 3 inbound and 3 outbound and we have already 1 backlog tickets 
on 1st of May we haven't received any inbound and outbound. so the backlog will be as same as periouse date 

[v-kaiyue-msft]

Hi @Sailu123 ,

I can not help you very well. Maybe you can provide me with your pbix file?

Please remove sensitive data and do not log in to your account in Power BI Desktop when uploading pbix files.

All this is to better solve your problems.

I would be grateful if you get back to me quickly.

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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

[Sailu123]

i have shared the file via email

[v-kaiyue-msft]

Hi @Sailu123 ,

Sorry, I didn't receive your email, you can upload the pbix with the sensitive data removed directly in the reply to this thread. So that we can better help you to solve the problem.

Best Regards,

Clara Gong

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

[Sailu123]

https://drive.google.com/file/d/1hnC9nFa04LeRb0aAJvh0EfeKZQoFidhT/view?usp=sharing 

[Sailu123]

Team, Can you please support me to find the solution