cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mohammadyousaf
Helper I
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. 

 

NameStart DateFinish Date
A25/08/2005/09/20
B15/09/2015/02/21
C07/12/2015/03/21
D20/12/2018/04/21
E20/12/20 
1 ACCEPTED SOLUTION
Eyelyn9
Community Support
Community Support

Hi @mohammadyousaf ,

 

Hope below is what you expected:

Eyelyn9_0-1631256292469.png

 

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.

View solution in original post

7 REPLIES 7
Eyelyn9
Community Support
Community Support

Hi @mohammadyousaf ,

 

Hope below is what you expected:

Eyelyn9_0-1631256292469.png

 

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.

View solution in original post

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. 

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. 

 

 

 

mohammadyousaf
Helper I
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.  

parry2k
Super User
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.





mohammadyousaf
Helper I
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.

 

mohammadyousaf_0-1631034613214.png

 

 

 

parry2k
Super User
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
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

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.