Forum Discussion

Randomlee's avatar
Randomlee
New Member
11 months ago
Solved

date roll up issue

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_IDORGANISATIONIS_ACTIVEIS_ELIGIBLEIS_COMPELTECOMPLETION_DATE
009e2ed0CORMACK & SONTRUETRUETRUE18/10/2024 00:00
00a0100eCORMACK & SONTRUETRUEFALSE 
00a3f0fbCORMACK & SONTRUEFALSETRUE16/10/2024 00:00
Need Help
  • johnt75's avatar
    johnt75
    11 months ago

    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' )
)

3 Replies

  • johnt75's avatar
    johnt75
    Super User
    11 months ago

    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's avatar
      Randomlee
      New Member
      11 months ago

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

  • Randomlee's avatar
    Randomlee
    New Member
    11 months ago

    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.