Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
bharukc
Helper I
Helper I

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

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

7 REPLIES 7
MarkLaf
Solution Sage
Solution Sage

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 )

 

 

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) 

 

 

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_1-1683354961426.png

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:

FreemanZ_0-1683354730265.png

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
)

 

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

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.