cancel
Showing results for
Search instead for
Did you mean:
Helper I

## Taking Blank Value from Date Column

Hi everyone,

Can someone please help me to calculate the following from below table.

1. If Name Value is not selected, it should calculate from finish date and return "In Progress" because there is no final date in the last item and item E is still in progress.

2. If any Name Value selected, it should show relevant date or N/A in case of Blank.

I want to put these values on card.

Thank you.

 Name Start Date Finish Date A 25/08/20 05/09/20 B 15/09/20 15/02/21 C 07/12/20 15/03/21 D 20/12/20 18/04/21 E 20/12/20
1 ACCEPTED SOLUTION
Community Support

Hope below is what you expected:

1. Create a rank measure:

``````Rank =
VAR _last =
RANKX (
ALLSELECTED ( 'Table' ),
CALCULATE ( MAX ( ( 'Table'[Start Date] ) ) ),
,
ASC,
DENSE
)
RETURN
IF (
MAX ( 'Table'[Finish Date] ) = BLANK (),
MAXX ( ALLSELECTED ( 'Table' ), _last ) + 1,
_last
)
``````

2. A measure for Card visual:

``````Measure =
VAR _lastFinish =
CALCULATE (
MAX ( 'Table'[Finish Date] ),
FILTER ( 'Table', [Rank] = MAXX ( ALL ( 'Table' ), [Rank] ) )
)
VAR _ifinprogress =
IF (
_lastFinish = BLANK (),
"In Progress",
FORMAT ( _lastFinish, "m/dd/yyyy" )
)
RETURN
IF (
ISFILTERED ( 'Table'[Name] ),
IF (
MAX ( 'Table'[Finish Date] ) = BLANK (),
"N/A",
FORMAT ( MAX ( 'Table'[Finish Date] ), "m/dd/yyyy" )
),
_ifinprogress
)
``````

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

7 REPLIES 7
Community Support

Hope below is what you expected:

1. Create a rank measure:

``````Rank =
VAR _last =
RANKX (
ALLSELECTED ( 'Table' ),
CALCULATE ( MAX ( ( 'Table'[Start Date] ) ) ),
,
ASC,
DENSE
)
RETURN
IF (
MAX ( 'Table'[Finish Date] ) = BLANK (),
MAXX ( ALLSELECTED ( 'Table' ), _last ) + 1,
_last
)
``````

2. A measure for Card visual:

``````Measure =
VAR _lastFinish =
CALCULATE (
MAX ( 'Table'[Finish Date] ),
FILTER ( 'Table', [Rank] = MAXX ( ALL ( 'Table' ), [Rank] ) )
)
VAR _ifinprogress =
IF (
_lastFinish = BLANK (),
"In Progress",
FORMAT ( _lastFinish, "m/dd/yyyy" )
)
RETURN
IF (
ISFILTERED ( 'Table'[Name] ),
IF (
MAX ( 'Table'[Finish Date] ) = BLANK (),
"N/A",
FORMAT ( MAX ( 'Table'[Finish Date] ), "m/dd/yyyy" )
),
_ifinprogress
)
``````

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

Helper I

it worked as intended, it was my  undertanding that I didnt get it.. adjust few tables and I am good to go.

Once Again, thank you.

Helper I

Really Thank you, take a bow... worked perfectly. Thank you for the hardwork. Much appreciated.

I have another question:

Just in case Filter is applied and multiple values selected

If Actual completion of any of multiple values is null, it should show "In Progress" , otherwise if all values are present in completion date, it should show the Max completion date.

Thank you so much.

Helper I

Yes last itme is defined agains the name column.

I am assuming system checks names, than check the dates, if all dates are present, pick the Max date, if not all dates are present agains the names, display "NA".

OR

I will have to do it for each page but below can also help me to resolve.

If row E in column name has blank finish date, Actual Completion should be "In Progress", else date.

Super User

@mohammadyousaf you have to have logical explanation of what defines the last? We are not working with assumptions, everything has to be logical, no?

Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helper I

Hi Parry, there is no rule of it but the last column is usually the last item to be finished off.. let's assume E is the item if it has the date, it means this item is completed.

I have already manged to get all finished dates when any item from name column is slected, but I yet to get the blank date if no item is selected on my slicer.

Here in the below screnshot, it is taking the last finish date which is D (Max Date), however actually the project is not finished so I want to replace the Actual finish date with "In Progress" unless item E has a date.

Super User

@mohammadyousaf how do you define the last item? Is it the name column, sorted alphabetically?

Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

## Helpful resources

Announcements

#### Launching new user group features

Learn how to create your own user groups today!

#### Check it Out!

Click here to read more about the November 2021 Updates!

#### Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors