date roll up issue

[Randomlee]

I'm reaching out here after about 5 days of tyring to unpick the issue myself with lots of rebuilds, googling, different measures, calculated columns etc and still failing. 

I have data in a format similar to the table below. I created a couple of measures 

Eligible = CALCULATE(

    DISTINCTCOUNT(data[PERSON_ID]),

    data[IS_ELIGIBLE] = TRUE(),

    data[IS_ACTIVE] = TRUE(),

    all('Date'[Date])

)

which is my whole head count and doesn't change over time.

and I have

Eligible Completed = CALCULATE(

    DISTINCTCOUNT(data[PERSON_ID]),

    data[IS_ELIGIBLE] = TRUE(),

    data[IS_ACTIVE] = TRUE(),

    data[IS_COMPELTE] = TRUE()

)

which tells me if the person has completed the training. the issue is when I come to lot it on a chart over time. As you can see below when the X axis is the actual date the eligible count is correct but when I roll up to month (or any other period) the count changes based on month. I am inferring it has something to do with the blank dates in my data but as I say haven't been able to figure it out in nearly a week so if there is anyone who can explain what I've done wrong I would be incredibly grateful for any help. 

 

PERSON_ID	ORGANISATION	IS_ACTIVE	IS_ELIGIBLE	IS_COMPELTE	COMPLETION_DATE
009e2ed0	CORMACK & SON	TRUE	TRUE	TRUE	18/10/2024 00:00
00a0100e	CORMACK & SON	TRUE	TRUE	FALSE
00a3f0fb	CORMACK & SON	TRUE	FALSE	TRUE	16/10/2024 00:00

[Randomlee]

THANK YOU SO MUCH!! I'm pretty sure what you've told me I saw in many a googling over the last week but clearly didn't see the wood for the trees.

[johnt75 · Accepted solution]

In your eligible measure you are only removing filters from the 'Date'[Date] column, which is why it works when you have the date column on the axis. You need to remove filters from the entire date table,

Eligible =
CALCULATE (
    DISTINCTCOUNT ( data[PERSON_ID] ),
    data[IS_ELIGIBLE] = TRUE (),
    data[IS_ACTIVE] = TRUE (),
    ALL ( 'Date' )
)

[Randomlee]

thank you thank you thank you! I'm so stupid! you're a life saver.