Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello, I need to calculate the difference between dates if certain conditions are met, sample data:
ID | Category | HC | Date |
1 | A | 0 | 01/01/2019 |
2 | A | 1 | 01/01/2019 |
3 | B | 1 | 01/01/2019 |
1 | A | 0 | 15/01/2019 |
2 | A | 0 | 15/01/2019 |
3 | B | 1 | 15/01/2019 |
1 | A | 1 | 01/02/2019 |
2 | A | 1 | 01/02/2019 |
3 | B | 1 | 01/02/2019 |
ID has to be the same and the condition is if HC is 0 then i need to calculate the difference in dates until its 1 for the same ID, this condition can happen multiple times over the year and I need to calculate the difference in every ocation
Solved! Go to Solution.
Hi @Anonymous ,
We can use the following measure to meet your requirement.
Days Until 1 =
VAR thisdate =
MAX ( 'Table'[Date] )
VAR thisid =
MAX ( 'Table'[ID] )
VAR thisHCvalue =
MIN ( 'Table'[HC] )
VAR next1date =
CALCULATE (
MAX ( 'Table'[Date] ),
FILTER(ALLSELECTED ( 'Table' ),
'Table'[ID]=thisid && 'Table'[HC]=0
))
RETURN
IF (
OR ( ISBLANK ( next1date ), thisHCvalue <> 0 ),
0,
DATEDIFF ( thisdate, next1date, DAY )
)
The result like this,
If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?
It will be helpful if you can show us the exact expected result.
BTW, pbix as attached.
Best regards,
Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
How about the result after you follow the suggestions mentioned in my original post?
Could you please provide more details or expected result about it If it doesn't meet your requirement?
Best regards,
Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
We can use the following measure to meet your requirement.
Days Until 1 =
VAR thisdate =
MAX ( 'Table'[Date] )
VAR thisid =
MAX ( 'Table'[ID] )
VAR thisHCvalue =
MIN ( 'Table'[HC] )
VAR next1date =
CALCULATE (
MAX ( 'Table'[Date] ),
FILTER(ALLSELECTED ( 'Table' ),
'Table'[ID]=thisid && 'Table'[HC]=0
))
RETURN
IF (
OR ( ISBLANK ( next1date ), thisHCvalue <> 0 ),
0,
DATEDIFF ( thisdate, next1date, DAY )
)
The result like this,
If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?
It will be helpful if you can show us the exact expected result.
BTW, pbix as attached.
Best regards,
Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Please try this measure expression in a table with Date and ID columns. It will get the shown result.
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
its not working for my i only get null values
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |