Forum Discussion
how to create a formula that dynamically calculates the backlog count based on the selected level
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
Hi Anonymous ,
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.
11 Replies
Hi Anonymous ,
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.
Hi Anonymous ,
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.
Hi Anonymous ,
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.
Hi Anonymous ,
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.
Hi Anonymous ,
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.