Calculate Latest date with condition and DATEDIFF

[Anonymous]

I have figured a measure to get the DATEDIFF between the Earliest and Latest Dates within the same column, if the state is "Closed Complete", by RPA #. 

The problem i am running into is that i only want to display the latest date if the state is "Closed Complete", and the latest date of that "Closed Complete" (many quotes within an RPA can be CC). If it does not have a date "Closed Complete" i want there to be a blank, meaning it is not complete. I am getting two records per line if there is a Closed Complete state (Ex: RPA00001890 and 1889). The lines that display 9 and 10 are correct, but it is the only line i would like to show.

Additional info:
-One RPA, can have several quotes. Each quote can have several states. The earliest date i am taking is the earliest start date, regardless of state because that is when the RPA is being started to work on. The latest date i am taking is the Max Start of Closed Complete because once a state enters that State it cannot go back (which is why it has no end date)
-The Active Date and Completed Date(technically latest date) columns are just the Variables split for visual purposes

[v-shex-msft]

Hi @Anonymous ,

Can you please share some sample data so that we can test to coding formula on it?

Regards,

Xiaoxin Sheng

[Anonymous]

Sample

Does that link work?

[Anonymous]

I have figured a measure to get the DATEDIFF between the Earliest and Latest Dates within the same column, if the state is "Closed Complete", by RPA #. 

Data SAMPLE- Everything needed should be in Quote State Table

The problem i am running into is that i only want to display the latest date if the state is "Closed Complete", and the latest date of that "Closed Complete" (many quotes within an RPA can be CC). If it does not have a date "Closed Complete" i want there to be a blank, meaning it is not complete. I am getting two records per line if there is a Closed Complete state (Ex: RPA00001890 and 1889). The lines that display 9 and 10 are correct, but it is the only line i would like to show.

Additional info:
-One RPA, can have several quotes. Each quote can have several states. The earliest date i am taking is the earliest start date, regardless of state because that is when the RPA is being started to work on. The latest date i am taking is the Max Start of Closed Complete because once a state enters that State it cannot go back (which is why it has no end date)
-The Active Date and Completed Date(technically latest date) columns are just the Variables split for visual purposes

[v-shex-msft · Accepted solution]

HI @Anonymous ,

I found there are multiple records in your table who has same 'RPA #' and same active/complete date.

If you drag calculated column to table visual with aggregate mode 'don't summarize', it should display two summarized records.(one is status 'closed complete', the blank one is other status which replaced by your if statement)

You can try to use following measure to achieve your requirement:

Actual Days2 =
VAR filtered =
    CALCULATETABLE (
        'QTE State',
        FILTER ( ALLSELECTED ( 'QTE State' ), [State] = "Closed Complete" ),
        VALUES ( 'QTE State'[RPA #] )
    )
RETURN
    IF (
        COUNTROWS ( filtered ) > 0,
        IF (
            ISINSCOPE ( 'QTE State'[State] ),
            IF (
                SELECTEDVALUE ( 'QTE State'[State] ) = "Closed Complete",
                DATEDIFF (
                    MAXX ( filtered, [Active Date] ),
                    MAXX ( filtered, [Completed Date] ),
                    DAY
                )
            ),
            DATEDIFF (
                MAXX ( filtered, [Active Date] ),
                MAXX ( filtered, [Completed Date] ),
                DAY
            )
        )
    )

BTW, power bi not support to create dynamic calculate column/table based on slicer or filter.

Regards,

Xiaoxin Sheng