Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi All,
I have a requirement to find the days between dates of the same column.
The first value should be the difference between the date and the date of the next step and the last value should be zero.
I have attached a sample pbix file with date here.
Could someone please suggest on how to achieve this using DAX.
Solved! Go to Solution.
Hi @POSPOS ,
Please try:
Column =
VAR _cur_protocol = 'export'[Protocol Number]
VAR _cur_index = 'export'[Index]
VAR _next_index = CALCULATE(MIN('export'[Index]),FILTER(ALL('export'),'export'[Protocol Number]=_cur_protocol && 'export'[Index]>_cur_index))
VAR _next_date = CALCULATE(MIN('export'[Status Date]),ALLEXCEPT('export','export'[Index]),'export'[Index]=_next_index)
VAR _result = COALESCE(DATEDIFF('export'[Status Date],_next_date,DAY),0)
RETURN
_result
Best Regards,
Gao
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data in the Power BI Forum -- China Power BI User Group
@Data-estDog - thank for the response.
When the dates are same, the values are not showing as expected. In this example, "Staff" comes before "Assigning", hence staff should be 9 and Assigning should be 0.
But 9 is repeated for both the statuses.
You would need a more granular date/time stamp. Or a sequence number (order the status was changed in) in order to achieve that.
@Data-estDog - Can we add a index col and then use this code ? as the statuses are not always in alphabetical order and can vary case by case.
Yes. I see that as the same as a sequence number. With the absence of time, its just something that gives indication of order.
Don't forget to mark this as the solution if and give me a thumbs up if I have helped you!
@Data-estDog -
Could you please suggest with the code using index. I have added index in the Power Query and tried reusing the code provided first, but does not seem to work fine.
Sure. I re-downloaded the pbix file... but no index column was there.
Hi @POSPOS ,
Please try:
Column =
VAR _cur_protocol = 'export'[Protocol Number]
VAR _cur_index = 'export'[Index]
VAR _next_index = CALCULATE(MIN('export'[Index]),FILTER(ALL('export'),'export'[Protocol Number]=_cur_protocol && 'export'[Index]>_cur_index))
VAR _next_date = CALCULATE(MIN('export'[Status Date]),ALLEXCEPT('export','export'[Index]),'export'[Index]=_next_index)
VAR _result = COALESCE(DATEDIFF('export'[Status Date],_next_date,DAY),0)
RETURN
_result
Best Regards,
Gao
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data in the Power BI Forum -- China Power BI User Group
If you want to assume status always happens in alphabetical order:
User | Count |
---|---|
93 | |
83 | |
77 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |