Min date to be a null value if any of the date value is a null value

[bharukc]

Is there a way to calculate a date to be set to null if one of the date is null?

i have close date

A: null

A: 1/2/2012

A: 1/4/2023

If per A has a null close date then the value to be null

[MarkLaf]

We probably need some more information, but I'll take a guess at what you are asking for and we can go from there.

I'm assuming your question is assuming data like:

Data = 

Product	Date
A	null
A	1/2/2012
A	1/4/2023
B	2/3/2014
B	3/4/2015
C	1/1/1900
C	null

And if you want to be able to create a table visual with Table[Product] and [Measure] in the fields such that it looks like:

Product	Measure
A	null
B	2/3/2014
C	null

Then the following may be what you want:

Measure = 
VAR _minDt = MIN( Data[Date] )
VAR _noBlankDts = CALCULATE( ISEMPTY( Data ), ISBLANK( Data[Date] ) )
RETURN
IF( _noBlankDts, _minDt )

[bharukc]

Hi @MarkLaf , the solution did not work for me.

here is my table

ID.       Work.    Close date.     Desired coumn
1         Ab.          
1.         Bb.         1/23/2012

1.         Cc.          3/23/2015

2.         Ab.          2/30/2012.     2/30/2012

2.          Ac.          2/23/2015.     2/30/2012

2.          Cc.          5/15/2018.      2/30/2012

if any of close date is empty, I would like the see a measure that would get a minimum value per id (includine null value) 

[FreemanZ]

if you would like to include other columns, try like:

measure2 = 
IF(
    BLANK() IN 
    CALCULATETABLE(
        VALUES(data[CloseDate]),
        ALLEXCEPT(data, data[ID])
    ),
    " ",
    CALCULATE(
        MIN(data[CloseDate]),
        ALLEXCEPT(data, data[ID])
    )
)

it worked like:

[FreemanZ]

hi @bharukc 

not sure if i fully get you, try to plot a table visual the ID column and a measure like:

measure = 
IF(
    BLANK() IN VALUES(data[CloseDate]),
    " ",
    CALCULATE(
        MIN(data[CloseDate]),
        data[ID]=MAX(data[ID])
    )
)

it worked like:

[MarkLaf]

It's not clear if you want this in a column or measure, but I think this should work in either:

Measure/Column = 
VAR _earliestRow = 
INDEX( 
    1, 
    ALL( Data[ID], Data[Close date] ), 
    ORDERBY( Data[Close date], ASC ), 
    KEEP, 
    PARTITIONBY( Data[ID] ) 
)
RETURN
CALCULATE( 
    MIN( Data[Close date] ),
    REMOVEFILTERS( Data ),
    _earliestRow
)

[MarkLaf]

Still figuring out the new window functions a bit.

I realized that INDEX will return a table of multiple rows if it's in a filter context that includes multiple partitions (e.g. this will happen in a total row in a table, subtotals in matrix, etc.).

Not sure if there is a better method, but using INDEX again to select one row from the resulting table of partitions (using same ordering logic from original INDEX) should provide a good summary result as needed:

Measure=
VAR _dir = 1 //  Last = 0   First = 1
VAR _dtByPartition = 
    INDEX( 
        1, ALL( Data[ID], Data[Close date] ), 
        ORDERBY( Data[Close date], _dir ), KEEP, PARTITIONBY( Data[ID] ) 
    )
VAR _selectPartition = 
    INDEX( 
        1, _dtByPartition, 
        ORDERBY( Data[Close date], _dir ), KEEP
    )
RETURN
SUMMARIZE( _selectPartition, Data[Close date] )

[FreemanZ]

hi @bharukc 

the data sample seems out of shape. check this:

https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...