Forum Discussion

ammar_cs_2004's avatar
ammar_cs_2004
Frequent Visitor
3 years ago
Solved

How to create start and end date columns from date column in Power BI?

I need to make the start date and end date columns from the date column. I have a table of Asset transfer 

Example Input:

 

Vr. No.Vr. DateTransaction NameAsset CodeTo CodeTo Name
1512731/12/2017 Asset Custodian Opening56521C1Company 1
15110/07/2019 Asset Transfer Custodian56521C2Company 2
590423/12/2021Asset Transfer Custodian56521C3Company 3

 

 

Expected Output:

 
Asset CodeIndexStart DateEnd DateCodeName
56521031/12/201709/07/2019C1Company 1
56521110/07/201922/12/2021C2Company 2
56521223/12/2021today dateC3Company 3

 

How to do it on power bi ?
 
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi ammar_cs_2004,

    You can try to use the following calculate column formula to get the end date:

    EndDate =
VAR nextDate =
    CALCULATE (
        MIN ( 'Table'[Vr. Date] ),
        FILTER (
            'Table',
            [Asset Code] = EARLIER ( 'Table'[Asset Code] )
                && [Vr. Date] > EARLIER ( 'Table'[Vr. Date] )
        )
    )
RETURN
    IF ( nextDate <> BLANK (), nextDate - 1, TODAY () )

    Regards,

    Xiaoxin Sheng

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi ammar_cs_2004,

    You can try to use the following calculate column formula to get the end date:

    EndDate =
VAR nextDate =
    CALCULATE (
        MIN ( 'Table'[Vr. Date] ),
        FILTER (
            'Table',
            [Asset Code] = EARLIER ( 'Table'[Asset Code] )
                && [Vr. Date] > EARLIER ( 'Table'[Vr. Date] )
        )
    )
RETURN
    IF ( nextDate <> BLANK (), nextDate - 1, TODAY () )

    Regards,

    Xiaoxin Sheng