DistinctCount, Averaging trouble

[mfarr76]

Hello,
I am attempting to use the DistinctCount expression to determine the number of properties (Serial Numbers in the attached spreadsheet) that are contributing to an aggregated column. The properties all start on day 1 but turn off at different times, therefore leaving the aggregated column jagged or stair-step like when they turn off (see attached workbook for an example).

I ultimately want to truncate the aggregated column when the when the contributing properties are less than the max, eliminating the late time data. I am using the following formula but it isn’t filter correctly (I think) because I get the same value for all the days even when properties are not contributing.

Any help would be greatly appreciated!

=
CALCULATE (
DISTINCTCOUNT ( Table1[SERIAL_NUM] ),
FILTER (
Table1,
Table1[SUB_GROUP] = EARLIER ( Table1[SUB_GROUP] )
&& Table1[DAYS] <= EARLIER ( Table1[DAYS] )
)
)


https://www.dropbox.com/sh/c6c791qaa506535/AADwr1Lzfixfp957ngZSt0o1a?dl=0

Thanks,
Michael

 

[Ozeroth]

Hi Michael, your dropbox link seems to be pointing to an empty folder. Could you post a corrected link?

 

[mfarr76]

Hi Michael, your dropbox link seems to be pointing to an empty folder. Could you post a corrected link?

My apologizes, I forgot to move the file into the folder. It has been corrected, please use the link above.