March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Register NowGet certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hello,
This is a sample data, I want to populate two columns here Open Date and Close Date with following condition
= if status is open, then return date in "Open Date" column but for each unique A_ID and B_ID, please see the sample below.
so basically for each unique A_ID and B_ID there are two statuses I want to populate date basis OPen or Close condition considering A_ID and B_ID
Any help would be highly appreciated. Thank you.
Solved! Go to Solution.
Hi @MonaliC Try below code:
For openDate:
For Close Date:
But for ID 103, there is 4 rows with only one close. So all 4 rows will have that close date and for open date first will be used.
Hope this helps!!
If this solved your problem, Please accept it as a solution!!
Best Regards,
Shahariar Hafiz
Hi @MonaliC Try below code:
For openDate:
For Close Date:
But for ID 103, there is 4 rows with only one close. So all 4 rows will have that close date and for open date first will be used.
Hope this helps!!
If this solved your problem, Please accept it as a solution!!
Best Regards,
Shahariar Hafiz
@shafiz_p Thanks much. This works. I am accepting it as solution.
Also for 103 as you see there are 4 of them, so the idea is that we need to consider pair- Open and Close so for first pair open close will be the earliest open and earliest close and for second pair second earliest open and second earliest close and so on. How can I get that result? could you please help.
@MonaliC You need PairID to correctly Identify each pair. If you have single pair then then mark it 1 and if you have multiple pair, then mark it 1, 2, 3 etc.
First sorting the A_ID in ascending order in power query.
Then create calculated column "PairID". See image below:
Then update the open date and close date code. See images below:
Hope this helps!!
If this solved your problem, accept it as a solution and a kudos!!
Best Regards,
Shahariar Hafiz
@shafiz_p
All my IDs are alphanumeric, Pair_ID results isn't the same as yours, please see below:
This is an example same A_ID-103 above, but below example isn't giving correct pair as per status. Could you please help here. Appreciate it a lot. Thank you.
Hello @MonaliC Check out the below image, it is perfectly working for me.
Check your formula. Also, make sure that your ID column is unique. If problem still exist, try creating an Index column using power query and try to use that column as ID.
Hope this solve your problem!!
Best Regards,
Shahariar Hafiz
It worked now. Thanks much. It was probably my typo in the formula.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Check out the November 2024 Power BI update to learn about new features.
User | Count |
---|---|
118 | |
96 | |
82 | |
69 | |
61 |
User | Count |
---|---|
138 | |
115 | |
115 | |
99 | |
98 |