Forum Discussion

JCMT's avatar
JCMT
Frequent Visitor
1 year ago
Solved

Rolling DistinctCount + Filters

Hi All,    My dataset has a calendar table and a table containing records with various properties that are linked by date to the calendar table, I've setup a measure to output a rolling total count...
Need Help
  • JCMT's avatar
    JCMT
    1 year ago

    Still haven't identified exactly what the issue was but it seems it was something in the source dataset because when I started from scratch with a fresh copy of the data and re created the measures it all behaved as expected. 

     

    Ultimately I wanted a rolling average, here are the final measures: 

    Unique Record IDs = COALESCE(DISTINCTCOUNT(Records[ID]), 0)


Unique Record IDs Rolling 7D Average =
DIVIDE(
    CALCULATE(
        SUMX(
            VALUES('Calendar'[Date]), [Unique Record IDs]
            ),
            REMOVEFILTERS('Calendar'[Date]),
            'Calendar'[Date] >= (max('Calendar'[Date])-6) && 'Calendar'[Date] <= max('Calendar'[Date])
            ),
        7
)

     

JCMT's avatar
JCMT
Frequent Visitor
1 year ago

Still haven't identified exactly what the issue was but it seems it was something in the source dataset because when I started from scratch with a fresh copy of the data and re created the measures it all behaved as expected. 

 

Ultimately I wanted a rolling average, here are the final measures: 

Unique Record IDs = COALESCE(DISTINCTCOUNT(Records[ID]), 0)


Unique Record IDs Rolling 7D Average =
DIVIDE(
    CALCULATE(
        SUMX(
            VALUES('Calendar'[Date]), [Unique Record IDs]
            ),
            REMOVEFILTERS('Calendar'[Date]),
            'Calendar'[Date] >= (max('Calendar'[Date])-6) && 'Calendar'[Date] <= max('Calendar'[Date])
            ),
        7
)