Filter when there is no data

[Anonymous]

I've got 3 tables

CaseID	start case	end case	start prod	end   prod
1	1-1-2020	31-7-2020	1-1-2020	31-5-2020
2	1-2-2020	30-6-2020	1-3-2020	30-4-2020

Activity Id	CaseID	Date
1	1	7-1-2020
2	1	3-2-2020
3	1	4-4-2020
4	1	5-5-2020
5	1	6-6-2020
6	2	2-3-2020

date	YYYY-MM
1-1-2020	2020-1
2-1-2020	2020-1
3-1-2020	2020-1
etc.

What I want to know is in which months there wasn't an activity while the month is valid. That means its between the start en end of the case AND between the start en end of a product. 

In this example I want the endresult to be:

Case Id	YYYY-MM
1	2020-3
2	2020-4

It's also possible to select a filter on YYYY-MM and that only the caseID's will apear when there is no activity in a valid period. Does anyone have an idea?

[Anonymous · Accepted solution]

NoActivity = 
VAR CaseTable = VALUES('Cases'[CaseID])
VAR MonthTable = VALUES('Datum'[YYYYMM])
VAR CJTable = CROSSJOIN(CaseTable,MonthTable)
VAR ActivityTable = ADDCOLUMNS(VALUES('Activities'),"YYYYMM",LOOKUPVALUE('Datum'[YYYYMM],'Datum'[Date],[  Date]))
VAR FindActivities = 
    ADDCOLUMNS(
        CJTable,
        "Activity",
            VAR CID = [CaseID]
            VAR YYMM = [YYYYMM]
            VAR FT = FILTER(ActivityTable,[  CaseID]=CID && [YYYYMM]=YYMM)
            RETURN
            CONCATENATEX(FT,[Activity Id])
    )
VAR ValidityCheckTable = 
    ADDCOLUMNS(
        FindActivities,
        "ValidityCheck",
        VAR YM = [YYYYMM]
        VAR SDOM = MINX(FILTER('Datum','Datum'[YYYYMM]=YM),'Datum'[Date])
        VAR LDOM = MAXX(FILTER('Datum','Datum'[YYYYMM]=YM),'Datum'[Date])
        VAR CSD = LOOKUPVALUE('Cases'[  start case],'Cases'[CaseID],[CaseID])
        VAR CED = LOOKUPVALUE('Cases'[  end case],'Cases'[CaseID],[CaseID])
        VAR PSD = LOOKUPVALUE('Cases'[  start prod],'Cases'[CaseID],[CaseID])
        VAR PED = LOOKUPVALUE('Cases'[  end   prod],'Cases'[CaseID],[CaseID])
        VAR CheckResult =
            IF(
                CSD<=SDOM && CED>=LDOM && PSD<=SDOM && PED>=LDOM,"Valid","Invalid")
        RETURN CheckResult
    )
VAR Result = FILTER(ValidityCheckTable,[ValidityCheck]="Valid" && [Activity]="")
RETURN
Result

Note: I have created a calculated table that gives the result shown above. If you want to use this for some calculation in other measures, you could use the same code to create temporary tables inside measures.

Change the table names and field names accordingly. This is based on your sample data.