Return blank value if earliest date and actual date value if later

[anonymous23]

Hello! I am working with monthly portfolios of accounts and looking to track additions to the population based on the earliest report an account appears in. However, if an account appears in the first report in our database, I do not want a value to be returned (either 0, blank, or null is sufficient).

I have created a measure to calculate the Earliest Date with the following formula: 

Earliest Date = FIRSTDATE('Additions/Disposals'[First Month)

I've tried to use the following formula to return the earliest month, exluding the first month of the data for each of the accounts and receive only the value 0 or an error message:

Addition = IF([First Month]=[Earliest Date],0,[First Month])

Here is my data set:

and my intended result in the Addition column would return as follows in order, so that I am able to count the number of additions in a period:

0

0

0

2/1/2022

2/1/2022

0

0

3/1/2022


Thank you for any assistance!

[Anonymous · Accepted solution]

Hi @anonymous23 ,

Please have a try. 

Create a measure.

Earliest Date =
VAR _mindate =
    MINX ( ALL ( 'Table' ), 'Table'[first month] )
RETURN
    IF (
        MAX ( 'Table'[first month] ) = _mindate,
        BLANK (),
        MAX ( 'Table'[first month] )
    )

Best Regards

Community Support Team _ Polly

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

[sturlaws]

Hi, @anonymous23,

could you try to write your calculated column like this:

addition =
VAR _firstMonth =
    CALCULATE ( MIN ( 'Table'[first month] ), ALL ( 'Table' ) )
RETURN
    IF ( 'Table'[first month] = _firstMonth, BLANK (), 'Table'[first month] )

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.