cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Calculate Latest date with condition and DATEDIFF

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.

PBI.PNGPBI2.PNG

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Calculate latest date depending on State/Condition, otherwise blank, remove duplicates

HI @cesarvaldez ,

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

12.png

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

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
Highlighted
Community Support
Community Support

Re: Calculate Latest date with condition and DATEDIFF

Hi @cesarvaldez ,

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

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Helper I
Helper I

Re: Calculate Latest date with condition and DATEDIFF

Sample

 

Does that link work?

Highlighted
Helper I
Helper I

Calculate latest date depending on State/Condition, otherwise blank, remove duplicates

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.

PBI.PNGPBI2.PNG

 

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

Highlighted
Community Support
Community Support

Re: Calculate latest date depending on State/Condition, otherwise blank, remove duplicates

HI @cesarvaldez ,

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

12.png

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

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors