Calculations for As of Dates

[Txtcher]

I have a simple model consisting of RS Cases (intakes), related Events and a date table:

I need to create a matrix showing backlog count of intakes. To determine whether an intake is counted as backlog at a certain point in time requires a complicated series of filters.

I have a report in Excel where I think I have accomplished it, but the report requires a merge between RS Cases and RS Events and I want to redesign it so I can avoid having PQ do that merge.

Here is the measure created in the Excel report with a model that looks like this (ignore the table called Cases_Without_Due_Dates - it is a separate query):

Here is the measure in that report:

Backlog:=CALCULATE(	
	COUNTROWS(RS_Cases),	
		SUMMARIZE(RS_Cases,RS_Cases[Program]),
			FILTER(All(RS_Cases),
			(RS_Cases[Due By]<=MAX(Date_Table[Date]) && RS_Cases[Status]="OPEN" ) ||
			(RS_Cases[Due By]<=MAX(Date_Table[Date]) && RS_Cases[Status]="CLOSED" && RS_Cases[Entrance Date]>Max(Date_Table[Date])) ||
			(RS_Cases[Due By]<=MAX(Date_Table[Date]) && ISBLANK(RS_Cases[Entrance Date]) && RS_Cases[Status]="CLOSED" && RS_Cases[Status Change Date]>MAX(Date_Table[Date])) 
		)
)

In the filter portion of this measure I am using a field called RS_Cases[Entrance Date]. This is a field that originates from the RS Events table in the model at the top of this post (matching field in that table is called [Survey Date]).

Can I recreate this measure in the redesigned model with the 2 separate tables? I cannot figure out how to refer to another table in the midst of filtering a different one.

[bhanu_gautam · Accepted solution]

@Txtcher , Try using

DAX
Backlog :=
CALCULATE(
COUNTROWS(RS_Cases),
SUMMARIZE(RS_Cases, RS_Cases[Program]),
FILTER(
ALL(RS_Cases),
(RS_Cases[Due By] <= MAX(Date_Table[Date]) && RS_Cases[Status] = "OPEN") ||
(RS_Cases[Due By] <= MAX(Date_Table[Date]) && RS_Cases[Status] = "CLOSED" &&
LOOKUPVALUE(RS_Events[Survey Date], RS_Events[Case ID], RS_Cases[Case ID]) > MAX(Date_Table[Date])) ||
(RS_Cases[Due By] <= MAX(Date_Table[Date]) && ISBLANK(LOOKUPVALUE(RS_Events[Survey Date], RS_Events[Case ID], RS_Cases[Case ID])) &&
RS_Cases[Status] = "CLOSED" && RS_Cases[Status Change Date] > MAX(Date_Table[Date]))
)
)

[Txtcher]

EDIT:  I think I have this figured out. The count is cumulative. When you break the count down by another group, it gets spread out. The overall grand total is the same, but the occurence of the backlog for those regions happened prior to Q2 of FY25.

💡😀

I have another question:

Why am I not getting the same counts if I add a 2nd grouping to the measure.

First measure grouped by RS Case[Program]:

Backlog = CALCULATE(
    COUNTROWS('RS Cases'),
    SUMMARIZE('RS Cases','RS Cases'[Program]),
    FILTER(
        ALL('RS Cases'),
        ('RS Cases'[Due By]<=Max('Calendar'[Date]) && 'RS Cases'[Status]="OPEN") ||
        ('RS Cases'[Due By]<=Max('Calendar'[Date]) && 'RS Cases'[Status]="CLOSED" &&
        LOOKUPVALUE('RS Events'[Survey_Date__c], 'RS Events'[Id],'RS Cases'[RS Event ID])> Max('Calendar'[Date])) ||
        'RS Cases'[Due By]<=Max('Calendar'[Date]) && ISBLANK(LOOKUPVALUE('RS Events'[Survey_Date__c], 'RS Events'[Id], 'RS Cases'[RS Event ID])) &&
        'RS Cases'[Status]="CLOSED" && 'RS Cases'[Status Change Date]>max('Calendar'[Date]))
    )

Here are the results in a table visual (which are correct):

But if I create a new measure by adding a 2nd grouping for Region:

Region Backlog = CALCULATE(
    COUNTROWS('RS Cases'),
    SUMMARIZE('RS Cases','RS Cases'[Program], 'RS Cases'[Region]),
    FILTER(
        ALL('RS Cases'),
        ('RS Cases'[Due By]<=Max('Calendar'[Date]) && 'RS Cases'[Status]="OPEN") ||
        ('RS Cases'[Due By]<=Max('Calendar'[Date]) && 'RS Cases'[Status]="CLOSED" &&
        LOOKUPVALUE('RS Events'[Survey_Date__c], 'RS Events'[Id],'RS Cases'[RS Event ID])> Max('Calendar'[Date])) ||
        'RS Cases'[Due By]<=Max('Calendar'[Date]) && ISBLANK(LOOKUPVALUE('RS Events'[Survey_Date__c], 'RS Events'[Id], 'RS Cases'[RS Event ID])) &&
        'RS Cases'[Status]="CLOSED" && 'RS Cases'[Status Change Date]>max('Calendar'[Date]))
    )

I get this result in the table visual with the same filters on the visual (FY2025, Q2, DAHS-ISS):

The expected result should include these records:

All 3 have a status of "OPEN" and are past their Due By dates. 

[Txtcher]

Thank you so much! 😀 I think that worked. I will do some more testing.