Power BI DAX query

[powerbi_enthu]

Hi Folks,

I want to manipulate my column value to get it into new column:

Below is my data.

I need to create "event end date" column with end date as 1 day prior to next event's start date within an "event group"

Company	event group	event start date (mm-dd-yyyy)
ABC mkt ltd.	planning event 1	11/11/2021
ABC mkt ltd.	planning event 2	11/14/2021
ABC mkt ltd.	planning event 3	11/16/2021
ABC mkt ltd.	planning event 4	11/25/2021
ABC mkt ltd.	execution event 1	10/10/2021
ABC mkt ltd.	execution event 2	10/15/2021
ABC mkt ltd.	execution event 3	10/17/2021
ABC mkt ltd.	execution event 4	10/21/2021

I want to derive column "event end date" in seperate column based on below logic:

Planning event 1 end date should be: 1 day prior to planning event 2 start date.

Planning event 2 end date should be:  today's date since it's the last event of the category planning

Company	event group	event start date (mm-dd-yyyy)	event end date (mm-dd-yyyy)
ABC mkt ltd.	planning event 1	11/11/2021	11/13/2021(one day less than event 2 "start date")
ABC mkt ltd.	planning event 2	11/14/2021	11/15/2021
ABC mkt ltd.	planning event 3	11/16/2021	11/24/2021
ABC mkt ltd.	planning event 4	11/25/2021	Today's date
ABC mkt ltd.	execution event 1	10/10/2021	10/14/2021
ABC mkt ltd.	execution event 2	10/15/2021	10/16/2021
ABC mkt ltd.	execution event 3	10/17/2021	10/20/2021
ABC mkt ltd.	execution event 4	10/21/2021	Today's date

@mblydt-hansen : I saw your solution which could help (https://community.powerbi.com/t5/Desktop/Date-difference-between-values-in-same-column/td-p/454940 )but not exactly giving me desired result as above, can you please help? 

[v-henryk-mstf · Accepted solution]

Hi @powerbi_enthu ,

According to my test, I need to first extract the event group field in power query as the basis for grouping, and then sort the group. Then create the following dax formula in desktop with the following reference:

Column =
RANKX (
    FILTER ( ALL ( 'Table' ), 'Table'[Group] = EARLIER ( 'Table'[Group] ) ),
    'Table'[event start date],
    ,
    ASC,
    DENSE
)

Col_result = 
VAR cur_index = 'Table'[Column] + 1
RETURN
    IF (
        'Table'[Column] = 4,
        TODAY (),
        CALCULATE (
            MAX ( 'Table'[event start date] ) - 1,
            FILTER (
                ALL ( 'Table' ),
                'Table'[Column] = cur_index
                    && 'Table'[Group] = EARLIER ( 'Table'[Group] )
            )
        )
    )

If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[kushal_nagendra]

I'm also facing the same issue. Can anyone pls help