cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
FeroSK Frequent Visitor
Frequent Visitor

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

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Calculated column for Start Date, End Date an Status

HI @FeroSK ,

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

17.png

Regards,

Xiaoxin Sheng

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



For learning resources/Release notes, please visit: | |

View solution in original post

4 REPLIES 4
Community Support Team
Community Support Team

Re: Calculated column for Start Date, End Date an Status

Hi @FeroSK ,

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

8.png

Regards,

Xiaoxin Sheng

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



For learning resources/Release notes, please visit: | |
FeroSK Frequent Visitor
Frequent Visitor

Re: Calculated column for Start Date, End Date an Status

Hi @v-shex-msft 

 

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

Community Support Team
Community Support Team

Re: Calculated column for Start Date, End Date an Status

HI @FeroSK ,

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

17.png

Regards,

Xiaoxin Sheng

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



For learning resources/Release notes, please visit: | |

View solution in original post

FeroSK Frequent Visitor
Frequent Visitor

Re: Calculated column for Start Date, End Date an Status

Thank you very much @v-shex-msft . After small correction in IF in continue column, it work!

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 304 members 2,710 guests
Please welcome our newest community members: