Power BI get count of records based on measure within a certain range

0
votes

I am able to do this with calculated columns, but I'm wondering if it can simplified with a measure. I have a table with tasks that includes a column for the due date of the task and created a measure to calculate how many days until the due date: Days to Due Date = sumx('Data Submittals', datediff(TODAY(), 'Data Submittals'[Due Date], DAY)); this works as expected.

I want to get the count of records where the measure is within a certain date range in the future (0-30 days, 30-60 days, 60-90 days, etc.); these date buckets are in a table with a min and max for each bucket. Is it possible to create a single measure that identifies the count for each bucket and then add the date bucket name and count (the new measure) into a table? Looking for something like this:

Date Range Count
0 - 30 Days 1
30 - 60 Days 0
60 - 90 Days 2
90+ Days 2

I tried tweaking an example I saw using RANKX in a CALCULATE:

CALCULATE(count('Data Submittals'[Title]),
    FILTER(VALUES('Data Submittals'[Title]),
        COUNTROWS(FILTER('Date Groups',
            RANKX(ALL('Data Submittals'[Title]), [Days to Due Date],, DESC) > 'Date Groups'[Min] &&
            RANKX(ALL('Data Submittals'[Title]), [Days to Due Date],, DESC) <= 'Date Groups'[Max])
        )
    )
)

This gives me an incorrect count for the first two groups only (it's including tasks with due dates in the past). I also tried using an IF in a COUNTX (COUNTX('Date Groups', if([Days to Due Date] > 'Date Groups'[Min] && [Days to Due Date] <= 'Date Groups'[Max], 1))), but this doesn't return anything. I feel like I might be overthinking it.

1
powerbidax

1 Answers

1
votes

Perhaps something like this:

Count per day range := 
VAR _min = SELECTEDVALUE ( 'Date Groups'[Min] )
VAR _max = SELECTEDVALUE ( 'Date Groups'[Max] )
RETURN
COUNTROWS ( 
    FILTER (
        ALL ( 'Data Submittals' ) , 
        [Days to Due Date] > _min &&
        [Days to Due Date] <= _max
    )
)