Forum Discussion
DAX Count with MIN Date
Hi, I have Column A (Date) and Column B (Serial Number)
| Date | Serial |
| 01/02/2019 | EA12891803 |
| 02/02/2019 | EA12891804 |
| 03/02/2019 | EA12891805 |
| 04/02/2019 | EA12891806 |
| 05/02/2019 | EA12891807 |
| 06/02/2019 | EA12891808 |
| 07/02/2019 | EA12891809 |
| 08/02/2019 | EA12891810 |
| 09/02/2019 | EA12891811 |
| 01/01/2019 | EA12891812 |
| 02/01/2019 | EA12891813 |
| 03/01/2019 | EA12891814 |
| 04/01/2019 | EA12891815 |
| 05/01/2019 | EA12891816 |
| 06/01/2019 | EA12891817 |
| 07/01/2019 | EA12891818 |
| 08/01/2019 | EA12891819 |
| 01/01/2019 | EA12891856 |
I am hoping to get some solution to "Count Serial Number based on MIN date"
Expected Outcome
Date Serial
01/01/2019 2
And in Weeks Behind is it possible to show blank field if Oldest Case don't have any date? I am not sure why I am seeing 6,464.
As variant for
assuming no other filters
Oldest Case:=MIN( 'Table'[Date] ) Weeks behind:=INT( (TODAY() - MIN( 'Table'[Date] ) )/7 ) In Queue:=VAR minDate=MIN( 'Table'[Date] ) RETURN COUNTROWS( FILTER( 'Table', 'Table'[Date] = minDate) )
11 Replies
Re. #1 Count Serial Number based on MIN date. Variants with the https://learn.microsoft.com/en-us/dax/countx-function-dax functions:
Count duplicates:
=COUNTX ( FILTER ( ALL ( 'Table' ), 'Table'[Date] = MINX ( ALL ( 'Table'[Date] ), [Date] ) ), [Serial] )Count distincts:
=COUNTX ( DISTINCT ( FILTER ( ALL ( 'Table' ), 'Table'[Date] = MINX ( ALL ( 'Table'[Date] ), [Date] ) ) ), [Serial] )Hi mouzzampk
#1 Count Serial Number based on MIN date
If you have duplicates and want to count them:
=VAR MinOfAllDates = CALCULATE ( MIN ( 'Table'[Date] ), ALL ( 'Table'[Serial] ) ) RETURN CALCULATE( COUNT( 'Table'[Serial] ), FILTER ( ALL ( 'Table' ), 'Table'[Date] = MinOfAllDates ) )If you don't want to count potential duplicates: same as above after replacing COUNT with DISTINCTCOUNT
#2 And in Weeks Behind is it possible to show blank field if Oldest Case don't have any date? I am not sure why I am seeing 6,464
Numeric value of TODAY(): 45,246
Numeric value of a blank in calc.: 0
(45,246 - 0) / 7 = 6,463.714286 (rounded up = 6,464)
Difficult to say for sure with your picture only but try the following:
=IF( ISBLANK([Oldest Case]), BLANK(), DIVIDE( (TODAY() - [Oldest Case]), 7) )If that doesn't work share your workbook (remove sensitive info.) please
As variant for
assuming no other filters
Oldest Case:=MIN( 'Table'[Date] ) Weeks behind:=INT( (TODAY() - MIN( 'Table'[Date] ) )/7 ) In Queue:=VAR minDate=MIN( 'Table'[Date] ) RETURN COUNTROWS( FILTER( 'Table', 'Table'[Date] = minDate) )
