Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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?
Solved! Go to 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.
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.
I'm also facing the same issue. Can anyone pls help
User | Count |
---|---|
141 | |
70 | |
69 | |
53 | |
52 |
User | Count |
---|---|
208 | |
94 | |
64 | |
60 | |
57 |