Dax formula for only showing the max date when all activities has been completed

[Anonymous]

Hello,

I have not been able to find a solution for this yet, so I would appreciate if someone could guide me a bit towards a solution.

So I have formula that gives me the max date and that works fine. However, I am using a slicer that containts planning activities for a product. At a point in time, the product is finish (all activities are completed). That takes time, and bit by bit activities are completed. What I need is my max date card / dax to, is to only show the max date when all activities are completed. 

Cheers,

Carsten

[NikhilChenna · Accepted solution]

Hi @Anonymous , I think you should create the below .
1. Calculated column,
Completed check = IF( table1[date]<>BLANK(),"Completed",BLANK())

2. Calculated measure,

Max Date = IF( table1[Completed check]<>BLANK(), MAX(table1[date], 

                        MAX(table1[date])

Regards,

Nikhil Chenna

Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!

[NikhilChenna · Accepted solution]

Hi @Anonymous , I think you should create the below .
1. Calculated column,
Completed check = IF( table1[date]<>BLANK(),"Completed",BLANK())

2. Calculated measure,

Max Date = IF( table1[Completed check]<>BLANK(), MAX(table1[date], 

                        MAX(table1[date])

Regards,

Nikhil Chenna

Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!

[Anonymous · Accepted solution]

Sorry for the late response, however this is not a solution for me. I am not able to create a calculated column (is using a live connection to a central dataset). However, after some more "googleing" and help from a colleague, this is the solution:

actual max finish date with selected value = IF (

    COUNTBLANK ( 'fact activity monitoring'[actual finish date] ) < 1

        && ISBLANK ( SELECTEDVALUE ( activity[activity code desc] ) ),

    [actual max finish date],

    IF (

        NOT ( ISBLANK ( SELECTEDVALUE ( activity[activity code desc] ) ) ),

        [actual max finish date],

        BLANK ()

    )

)

[Anonymous]

Thanks, I have tried to use your suggested approach, but when I e.g. select an activity that has a date, it says blank and I need it to show the date when it is not blank. So e.g. I have a procuct with 6 activities, if one product has one or more activities without a date, I need the card to display e.g. "blank", however if I select one of the activities that does have a date, then I need the card to display the date. If all activities has a date, I need to see the max date if not activity is selected, and if I then select an activity I need the card to display the maxdate (which is the lowest level and there for the only date available, the exact date). Does that makes sense? It's complicated 🐵

[FreemanZ]

you may wrap it with if condition, something like this:

NewMeasure

VAR MaxDate =

CALCULATE(

    MAX (TableName[Date]),

    ALL()

)

RETURN

IF(

     [YourMeasure] = MaxDate,

     [YourMeasure]

)