Intersect - except - calculatetable

DickyMoo

New member
Joined
Mar 7, 2016
Messages
27
Reaction score
0
Points
0
Location
London
Hi All,

I am struggling with a calculation for number of exam candidates who became members in a particular month, I have the below:

Joiners:=
COUNTROWS (
INTERSECT (
VALUES ( Exams[Contact Number] ),
EXCEPT (
VALUES ( Members[Contact Number] ),
CALCULATETABLE (
VALUES ( Members[Contact Number] ),
Members[Status] = "Current/Active",
FILTER( ALL( Members ), Members[Financial Period] < MIN ( Calendar[Financial Period] ) )
)
)
)
)

The logic is - Everyone who sat this exam AND is active in this month BUT was not active in any previous month.

I'm stuck on the bold line, the table of members active in previous months. Does anyone have any suggestions?

Thanks
Rich
 
Can you post the workbook for us to work with?
 
Hi,

Here is a much chopped down version. I have removed the Current/Active column to keep things simple.

The Result page shows the structure of my desired output.

Thanks
Rich
 

Attachments

  • Members.xlsx
    488 KB · Views: 12
ok nailed it, had to bone up on filter context etc and sit in a dark room and think for a while
Code:
Exam Joiners = 
COUNTROWS (
    INTERSECT (
        VALUES ( Exams[Contact Number] ),
        EXCEPT (
            CALCULATETABLE (
                VALUES ( 'Member Data'[Contact Number] ),
                'Member Data'[Status] = "Current/Active"
            ),
            CALCULATETABLE (
                VALUES ( 'Member Data'[Contact Number] ),
                FILTER (
                    ALL ( 'Member Data' ),
                    AND (
                        'Member Data'[Financial Period] < MIN ( Calendar[Financial Period] ),
                        'Member Data'[Status] = "Current/Active"
                    )
                )
            )
        )
    )
)
 
Back
Top