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 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 
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.

7 Replies

  • 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. 

  • 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.

     

     

     

     

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    5 years ago

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

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

    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.