Backlog Calculation

[Ru_Petrakieva]

Dear Community, 

Hope my post finds you well and healthy. 

I have the following problem. I want to show how many tickets were opened on a particular date and how many were closed if any.

NOTE: I have created calendar table.

1. I have 1 core measure which is # Of Cases =  =DISTINCTCOUNT(OpenedAndClosedTicketsF[Case Number])

2. Then I created Open Cases =CALCULATE([# Of Cases],USERELATIONSHIP(CalendarTable[Dates],OpenedAndClosedTicketsF[Date/Time Opened.1]))

3. And Closed Cases =CALCULATE([# Of Cases],USERELATIONSHIP(CalendarTable[Dates],OpenedAndClosedTicketsF[Date/Time Closed.1]))

4. Then I created the measure Open Minus Closed =[Open Cases] - [Closed Cases]

5. And lastly I created the backlog measure as it follows: =CALCULATE([Open Minus Closed ],FILTER(ALL(CalendarTable),CalendarTable[Dates]<=MAX(CalendarTable[Dates])))

Everything works perfectly fine If I do not filter the table by any period or year.

As the backlog measure considers all the previous dates...

If I am about to filter the table only by Aug-2021, these are the results I am getting:

Obviously when I filter the measure is not considering the previous backlog and this is my problem. 

Would you please help me correct it?

All due respect,

Radoslava

[Anonymous]

It really is important which table you take fields from and put in your slicers and visuals. I'll give you a hint: Fact tables should always have all their columns hidden (never put on the canvas) and slicing and dicing should always be performed through dimensions. Stick to this rule and you'll be less surprised.

By the way, if you have to put columns from a fact table into a visual.... well, that means your design is incorrect.