Forum Discussion

mohammadyousaf's avatar
mohammadyousaf
Icon for Resolver II rankResolver II
5 years ago
Solved

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 the...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi mohammadyousaf ,

     

    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.

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi mohammadyousaf ,

 

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.

  • mohammadyousaf's avatar
    mohammadyousaf
    Icon for Resolver II rankResolver II
    5 years ago

    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's avatar
    mohammadyousaf
    Icon for Resolver II rankResolver II
    5 years ago

    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.