Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Calculated column for Start Date, End Date an Status

Hi All,

 

Do you happend to know how to find Start Date, End Date assign status "Open" or "Completed" from my data?

I have 3 columns: Report number, Report Date, ID

Report NumberidDate
1A2/15/2019
1B2/15/2019
1C2/15/2019
1D2/15/2019
1E2/15/2019
1F2/15/2019
2A2/23/2019
2B2/23/2019
2C2/23/2019
2D2/23/2019
2E2/23/2019
2F2/23/2019
2G2/23/2019
3B3/3/2019
3C3/3/2019
3E3/3/2019
3M3/3/2019
4D3/8/2019
4A3/8/2019
4C3/8/2019
4M3/8/2019
4G3/8/2019

 

What i need?

I need to add Start Date, End Date an Status for all ID's.

Start Date is always equal  report date when ID's was add and until completed. Completed means ID's is not in next report. End Date is date of next report (it can be calculated as end of the week from report date). Biggest problem is, that ID's are aways repeating, so status is changing from open to completed and vice versa.

 

Please, look on table with results and with desribe column

Report NumberidReport DateStatusstart DateEnd DateDesribe
1A2/15/2019Open2/15/2019 it is new, so it have "Open" status
1B2/15/2019Open2/15/2019 it is new, so it have "Open" status
1C2/15/2019Open2/15/2019 it is new, so it have "Open" status
1D2/15/2019Open2/15/2019 it is new, so it have "Open" status
1E2/15/2019Open2/15/2019 it is new, so it have "Open" status
1F2/15/2019Open2/15/2019 it is new, so it have "Open" status
2A2/23/2019Completed2/15/20193/3/2019Continues from report 1, but not in next report "3", so "Completed" status
2B2/23/2019Open2/15/2019 Continues from report 1 and it is in next report, "Open" Status
2C2/23/2019Open2/15/2019 Continues from report 1 and it is in next report, "Open" Status
2D2/23/2019Completed2/15/20193/3/2019Continues from report 1, but not in next report "3", so "Completed" status
2E2/23/2019Open2/15/2019 Continues from report 1 and it is in next report, "Open" Status
2F2/23/2019Completed2/15/20193/3/2019Continues from report 1, but not in next report "3", so "Completed" status
2G2/23/2019Open2/23/20193/3/2019new, but not in next, so "Completed" w date of next report
3B3/3/2019Open2/15/2019 Continues from report 1 and it is in next report, "Open" Status
3C3/3/2019Open2/15/2019 Continues from report 1 and it is in next report, "Open" Status
3E3/3/2019Completed2/15/20193/8/2019Continues from report 1 and it is in next report, "Open" Status
3M3/3/2019Open3/3/2019 it is new, so it have "Open" status
4D3/8/2019Open3/8/2019 not in previous report, Open status
4A3/8/2019Open3/8/2019 not in previous report, Open status
4C3/8/2019Open2/15/2019 it is new, so it have "Open" status
4M3/8/2019Open3/3/2019 it is new, so it have "Open" status

 

Thank all of you for your time

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    HI Anonymous ,

    Actually, start date is more complex than end date. You need to check record continuous properties first and write formula based on continuous fields.

    Continue = 
VAR _previous =
    CALCULATETABLE (
        VALUES ( T2[id] ),
        FILTER ( ALL ( T2 ), [Report Number] = EARLIER ( T2[Report Number] ) - 1 )
    )
VAR _next =
    CALCULATETABLE (
        VALUES ( T2[id] ),
        FILTER ( ALL ( T2 ), [Report Number] = EARLIER ( T2[Report Number] ) + 1 )
    )
RETURN
    IF ( [id] IN _previous || [id] IN _next, 1, 0 )

Start date = 
VAR _temp =
    CALCULATE (
        MAX ( T2[Date] ),
        FILTER (
            ALL ( T2 ),
            [id] = EARLIER ( T2[id] )
                && [Date] < EARLIER ( [Date] )
                && [Continue] = 0
        )
    )
VAR _start =
    MAX (
        _temp,
        CALCULATE (
            MIN ( T2[Date] ),
            FILTER ( ALL ( T2 ), [id] = EARLIER ( [id] ) && [Date] <= EARLIER ( [Date] ) )
        )
    )
RETURN
    IF ( [Continue] <> 0, _start, [Date] )

    Regards,

    Xiaoxin Sheng

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi Anonymous ,

    You can use following calculated column formulas to achieve your requirement.

    Status =
VAR _next =
    CALCULATETABLE (
        VALUES ( T2[id] ),
        FILTER ( ALL ( T2 ), [Report Number] = EARLIER ( T2[Report Number] ) + 1 )
    )
RETURN
    IF (
        COUNTROWS ( _next ) > 0,
        IF ( [id] IN _next, "Open", "Completed" ),
        "Open"
    )

End Date = IF([Status]="Completed",[Date])

    Regards,

    Xiaoxin Sheng

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi Anonymous 

       

      thank you very much! You are amazing.

      Last thing, i need column Start date with repeating date from first open date until completed. Do you thing i could ask you to help me with this calculation?

       

      Fero

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        HI Anonymous ,

        Actually, start date is more complex than end date. You need to check record continuous properties first and write formula based on continuous fields.

        Continue = 
VAR _previous =
    CALCULATETABLE (
        VALUES ( T2[id] ),
        FILTER ( ALL ( T2 ), [Report Number] = EARLIER ( T2[Report Number] ) - 1 )
    )
VAR _next =
    CALCULATETABLE (
        VALUES ( T2[id] ),
        FILTER ( ALL ( T2 ), [Report Number] = EARLIER ( T2[Report Number] ) + 1 )
    )
RETURN
    IF ( [id] IN _previous || [id] IN _next, 1, 0 )

Start date = 
VAR _temp =
    CALCULATE (
        MAX ( T2[Date] ),
        FILTER (
            ALL ( T2 ),
            [id] = EARLIER ( T2[id] )
                && [Date] < EARLIER ( [Date] )
                && [Continue] = 0
        )
    )
VAR _start =
    MAX (
        _temp,
        CALCULATE (
            MIN ( T2[Date] ),
            FILTER ( ALL ( T2 ), [id] = EARLIER ( [id] ) && [Date] <= EARLIER ( [Date] ) )
        )
    )
RETURN
    IF ( [Continue] <> 0, _start, [Date] )

        Regards,

        Xiaoxin Sheng