topic Re: Min date to be a null value if any of the date value is a null value in DAX Commands and Tips

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

bharukc — Wed, 03 May 2023 15:05:51 GMT

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

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

MarkLaf — Wed, 03 May 2023 22:42:35 GMT

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 )

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

bharukc — Thu, 04 May 2023 12:25:15 GMT

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)

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

FreemanZ — Thu, 04 May 2023 14:42:37 GMT

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-p/963216

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

MarkLaf — Thu, 04 May 2023 22:12:31 GMT

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 )

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

MarkLaf — Fri, 05 May 2023 20:52:26 GMT

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

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

FreemanZ — Sat, 06 May 2023 06:32:43 GMT

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:

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

FreemanZ — Sat, 06 May 2023 06:36:21 GMT

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: