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.

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

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.