Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
powerbi_enthu
Regular Visitor

Power BI DAX query

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

Companyevent groupevent 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? 

 

1 ACCEPTED SOLUTION
v-henryk-mstf
Community Support
Community Support

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

vhenrykmstf_0-1636958978007.png


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.

View solution in original post

2 REPLIES 2
v-henryk-mstf
Community Support
Community Support

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

vhenrykmstf_0-1636958978007.png


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
Regular Visitor

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

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.