Forum Discussion

mabCOLONEL's avatar
mabCOLONEL
Frequent Visitor
3 years ago
Solved

Count dates past a dynamic date on another table with criteria.

Hi everyone.  I have a pretty basic need for a measure here.  I have two tables.  One has an index of location attributes.  It includes an 'end date'.  Another table is production data.  So actions t...
Need Help
  • mabCOLONEL's avatar
    mabCOLONEL
    3 years ago

    I made a measure for the 'end date' (End Date = 'Index'[End Date] and just added that to countrows measure:

    past end date = COUNTROWS(FILTER('Data Points','Data Points[Date] > 'Index'[End Date]))
Barthel's avatar
Barthel
Icon for Solution Sage rankSolution Sage
3 years ago

Hey mabCOLONEL,

You can create a measure for this that you can place in a matrix. This measure calculates per location the number of rows (actions) whose date is after the end date. And then takes the sum of that.

Past End = 
SUMX (
    KEEPFILTERS ( 'Index' ),
    CALCULATE (
        COUNTROWS ( 'Data Points' ),
        'Data Points'[Date] > EARLIER ( 'Index'[End Date] )
    )
)
mabCOLONEL's avatar
mabCOLONEL
Frequent Visitor
3 years ago

I made a measure for the 'end date' (End Date = 'Index'[End Date] and just added that to countrows measure:

past end date = COUNTROWS(FILTER('Data Points','Data Points[Date] > 'Index'[End Date]))