Forum Discussion

PBINewbie920's avatar
PBINewbie920
Icon for Helper I rankHelper I
4 years ago
Solved

Create Column for "in progress"

Hi! 

I have a table that has three fields: Date, ID & Type. I want to create a third column to determine which projects are still in progress (those projects that do not have type=billed)

 

For instance, project ABC was billed on 1/5, so it is no longer in progress. However project JKL does not have a billed date, so it is still in progress. 

DateProject IDTypeIn Progress?
1/1/2022ABCBookedNo
1/1/2022DEFBookedNo
1/1/2022JKLBookedYes
1/1/2022MNOBookedYes
1/1/2022POQBookedYes
1/5/2022ABCBilled 
1/5/2022DEFBilled 

 

Any ideas how to create a column for this?

Thank you!!!

Need Help
  • Shishir22's avatar
    Shishir22
    4 years ago

    Hello PBINewbie920 ,

     

    Please try below calculated column -

    In Progress =
VAR _Count =
    CALCULATE (
        COUNT ( 'Table'[Project ID] ),
        FILTER (
            'Table',
            'Table'[Type] = "Billed"
                && 'Table'[Project ID] = EARLIER ( 'Table'[Project ID] )
        )
    )
RETURN
    IF (
        FIRSTNONBLANK ( 'Table'[Type], 0 ) = "Booked"
            && ISBLANK ( _Count ),
        "Yes",
        "No"
    )

     

    Please mark it as solution if it solves your issue. Kudos are also appreciated.

     

     

1 Reply

  • Shishir22's avatar
    Shishir22
    Icon for Solution Sage rankSolution Sage
    4 years ago

    Hello PBINewbie920 ,

     

    Please try below calculated column -

    In Progress =
VAR _Count =
    CALCULATE (
        COUNT ( 'Table'[Project ID] ),
        FILTER (
            'Table',
            'Table'[Type] = "Billed"
                && 'Table'[Project ID] = EARLIER ( 'Table'[Project ID] )
        )
    )
RETURN
    IF (
        FIRSTNONBLANK ( 'Table'[Type], 0 ) = "Booked"
            && ISBLANK ( _Count ),
        "Yes",
        "No"
    )

     

    Please mark it as solution if it solves your issue. Kudos are also appreciated.

     

     

Related content