DAX Count with MIN Date

[mouzzampk]

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.

[#2]

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

[#3]

@mouzzampk 

Re. #1 Count Serial Number based on MIN date. Variants with the X 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]
)

[#4]

@Lorenzo Thank you so much 

Part 2 is fixed but In Queue seem like not outputting correct. I have 2 serial numbers for 24/10/2023 but only one is showing. I have used COUNTX not the top one.

[#5]

@mouzzampk 

In Queue seem like not outputting correct. I have 2 serial numbers for 24/10/2023 but only one is showing

Can't determine the cause with a picture only. Please attach your workbook or if you're not allowed upload and share it on OneDrive, Google Drive... and post the shared link here please

[#6 · Accepted solution]

@mouzzampk 

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) )

[#7]

@SergeiBaklan 

This works awesome
In Queue:=VAR minDate=MIN( 'Table'[Date] )
RETURN COUNTROWS( FILTER( 'Table', 'Table'[Date] = minDate) )

But for this one I am getting 6,463 when Oldest Field is empty.

[#8]

Thanks @SergeiBaklan I combined your formula with @Lorenzo  and its working perfectly fine.

=IF(ISBLANK([Oldest Case]),BLANK(),INT( (TODAY() - MIN( 'Outstanding Reps'[Date] ) )/7 ) )

Thank you

[#9]

Thank you so much for your help. Much appreciated.

[#10]

@mouzzampk 

You are welcome, thank you for sharing

[#11]

@mouzzampk Appreciate your feedback although my suggestions were obviously far too complex 😞

Regards

[#12]

@Lorenzo  because of you I learnt COUNTX and its so good to see such a positive community. 

Thank you again and apologies for not properly publishing the template. 

Best regards,

mouzzampk