Forum Discussion
Count Distinct date based on current row condition
Hello renegar
Give something like this a try.
Date Count = VAR _RowDate = 'Table'[Date] RETURN CALCULATE ( COUNTROWS ( VALUES ( 'Table'[Date] ) ), ALLEXCEPT ( 'Table', 'Table'[Category 1], 'Table'[Category 2] ), 'Table'[Date] < _RowDate )VALUES ( 'Table'[Date] ) gives us the unique list of dates for that row
we expand it to the range of Cat 1 and Cat 2 with ALLEXCEPT ( 'Table', 'Table'[Category 1], 'Table'[Category 2] )
then bring it back to just the lower dates with the 'Table'[Date] < _RowDateThen we count the rows left so the number of unique dates for that cat 1 and cat 2 that are lower than the current row date.
Hi renegar ,
Strange... maybe relationships the tables that form the sub measures? jdbuchanan71 any thoughts?
Hello renegar
Give something like this a try.
Date Count =
VAR _RowDate = 'Table'[Date]
RETURN
CALCULATE (
COUNTROWS ( VALUES ( 'Table'[Date] ) ),
ALLEXCEPT ( 'Table', 'Table'[Category 1], 'Table'[Category 2] ),
'Table'[Date] < _RowDate
)VALUES ( 'Table'[Date] ) gives us the unique list of dates for that row
we expand it to the range of Cat 1 and Cat 2 with ALLEXCEPT ( 'Table', 'Table'[Category 1], 'Table'[Category 2] )
then bring it back to just the lower dates with the 'Table'[Date] < _RowDate
Then we count the rows left so the number of unique dates for that cat 1 and cat 2 that are lower than the current row date.
