Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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

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 as solution to help other members find it more quickly.

View solution in original post

4 REPLIES 4
v-shex-msft
Community Support
Community Support

Hi @Anonymous ,

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 as solution to help other members find it more quickly.
Anonymous
Not applicable

Sample

 

Does that link work?

Anonymous
Not applicable

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

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

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 as solution to help other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.