Get the oldest date by credit for each year

[AmiraBedh]

I have 2 tables in dax :

Table Credit with column creditbureaureference
Table DueAmount with column with duevaluedate

the relationships between them
Table Credit (pkey) to DueAmount(creditfk) is 1 to many and the filter is in both directions

I am using the column creditbureaureference from table Credit and duevaluedate from DueAmount in a table visual :

creditbureaureference duevaluedate
1 04/02/2024
1 05/02/2024
1 06/02/2024
2 07/02/2024
2 25/12/2023

I want to get the oldest date of duevaluedate for each creditbureaureference like below :

creditbureaureference duevaluedate Flag
1 04/02/2024 1 --- comparing to the 3 rows of creditbureaureference = 1 this is the oldest date
1 05/02/2024 0
1 06/02/2024 0
1 04/02/2023 1
2 07/02/2024 0
2 25/12/2023 1 --- comparing to the 2 rows of creditbureaureference = 2 this is the oldest date

I used this calculated column :

Flag =
VAR MinDate =
    CALCULATE (
        MIN ( 'DueAmount'[duevaluedate] ),
        ALLEXCEPT ( 'DueAmount', 'DueAmount'[creditfk] )
    )
RETURN
    IF ( 'DueAmount'[duevaluedate] = MinDate, 1, 0 )

Now when I use this calculated column as a slicer :

Year Slicer = YEAR('DueAmount'[duevaluedate])

I get all the Flag column as 0 while the expected result should be to calculate the Flag by year  :

If I select 2024, it will show :

creditbureaureference duevaluedate
1                                  04/02/2024
2                                  07/02/2024

[AmiraBedh · Accepted solution]

I ended up using this calculated column :

Flag = 
VAR CurrentYear = YEAR('DueAmount'[duevaluedate])
VAR MinDatePerYearAndCredit =
    CALCULATE(
        MIN('DueAmount'[duevaluedate]),
        ALLEXCEPT('DueAmount', 'Credit'[creditbureaureference]),
        YEAR('DueAmount'[duevaluedate]) = CurrentYear
    )
RETURN
    IF('DueAmount'[duevaluedate] = MinDatePerYearAndCredit, 1, 0)

[v-yohua-msft]

Hi, @AmiraBedh 

Thanks for the reply from @amitchandak , please allow me to provide another insight:

Based on your information, I create table:

First create a calculate column named Year :

Year = YEAR('DueAmount'[duevaluedate])

Then create a new measure, try the following DAX:

Flag Measure = 
VAR SelectedYear = MAX('DueAmount'[Year]) 
VAR MinDatePerYear =
    CALCULATE(
        MIN('DueAmount'[duevaluedate]),
        ALLEXCEPT('DueAmount', 'DueAmount'[creditbureaureference]),
        YEAR('DueAmount'[duevaluedate]) = SelectedYear
    )
    VAR _Date = MIN('DueAmount'[duevaluedate])
RETURN
    IF(
        YEAR(_Date) = SelectedYear && _Date = MinDatePerYear,
        1
    )

And here is my preview:

How to Get Your Question Answered Quickly 

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AmiraBedh]

Thank you for your help, the problem is  the creditbureaureference exists only in the table Credit. How should I proceed in this way ?

[amitchandak]

@AmiraBedh , Try a measure like

First Date = var _max = minx(filter(ALLSELECTED('DueAmount'), 'DueAmount'[creditbureaureference ] = Max('DueAmount'[creditbureaureference ])), 'DueAmount'[duevaluedate])
return
CALCULATE(min('DueAmount'[duevaluedate]), filter(('DueAmount') , 'DueAmount'[duevaluedate] =_max))

[AmiraBedh]

Thank you for your help, the problem is  the creditbureaureference exists only in the table Credit. How should I proceed in this way ?