Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello all, this Measure is working as long as there are only 2 dates Min and Max, but I need to get the date before Max regardless if it's Min or not. 1 table, 1 date column and 1 category column. I have a date table but I am not using it here. Thanks for any help on this,
PreviosNameDays =
var _Last = MAX(Table[Created])
var _MinDate = CALCULATE (
MIN( Table[Created]),
ALLEXCEPT( 'Table', 'Table'[Name] ))
var _previousBKT = CALCULATE(MAX('Table'[Name]),Table[Created])
var _currentBKT = CALCULATE(MIN(Table[Name]),Table[created])
return
IF(_previousBKT =_currentBKT && _MinDate <> _Last,VALUE(UTCNOW()-_MinDate),
IF(_previousBKT =_currentBKT && _MinDate = _Last,VALUE(UTCNOW()-_MinDate),DATEDIFF(_MinDate,_Last,DAY)))
Solved! Go to Solution.
Thanks for clearer explanation, below you can try the new measure for the different of the days compare to previous ID.
Hope this can help you.
Hi @rixmcx59
If the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly. If not, please point it out.
Looking forward to your feedback.
Best Regards,
Henry
Hi @rixmcx59 ,
You can try formula like below:
Col = RANKX('Table','Table'[Date],,DESC,Dense)
M_ =
VAR min_ =
MIN ( 'Table'[Date] )
VAR max_ =
CALCULATE ( MAX ( 'Table'[Date] ), 'Table'[Col] = 3 )
RETURN
DATEDIFF ( min_, max_, DAY )
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.
Hello thank you for the reply, the last post in this thread has my sample data and and an explanation of what I am trying to do. I can see where RANKX can be useful but I cuold not get the desired result, I am trying to count the number of days in each category, please see my sample data posted earlier today.
Hi, you may try to use MAXX/MINX instead of MAX/MIN. and if you want to by category, you can add
var category1 = selectedvalue('category')
return
CALCULATE(IF(_previousBKT = _currentBKT && _MinDate = _Last,VALUE(UTCNOW()- _MinDate), DATEDIFF(_MinDate,_Last,DAY)), 'category' = 'category1')
Hope this can help you 😁
Hello, thanks for the reply. my goal is to return the date before max not min, but I can't use the earlier function in a measure (or can you?), below is my data simplified, in the table I need to compare ID 1 and 4 and get the datediff in days for aName
1 | aName | 7/30/22 |
2 | aName | 7/15/22 |
3 | aName | 7/1/22 |
4 | aName | 8/15/22 |
thanks for anu help on this
Hello, earlier function are normally used in the calculated column instead of measure. You may refer to the power bi that i had attached below, the measure i done is datediff based on the ID of the name.
Dummy data:
Sample measure result:
Measure:
.
Hope this can help you
Hello, thanks for the response. in my data Min(ID) will return the first record in the group, this is not the record I want, I am looking for previous date, as I add new records I am checking for change from the most recent.
Thanks
Maybe, you can show me what is the result you wanted from this table, i believe that i will have a better understanding on your requirement.
Thanks, I am testing your measure but not getting the right result. this is a better sample of the actual data. as I add new records I need to compare to the previous date, when I add ID 2, compare to ID 1, when I add ID 3, compare to ID 2. Below sample shows ID 5, I need to compare to ID 4. Using Min and Max return ID 1 and ID 5 which is not what I need to check for name change. Thank for your help
ID | Category | Date | Status | Days |
1 | aName | 7/27/22 | ||
2 | aName | 8/10/22 | No Change | 14 days |
3 | aName | 8/17/22 | No Change | 21 Days |
4 | bName | 8/25/22 | Change | 1 Days |
5 | bName | 9/1/22 | No Change | 6 Days |
Thanks for clearer explanation, below you can try the new measure for the different of the days compare to previous ID.
Hope this can help you.
Thank you, I will try this on Tuesday when I return to work and let you know.
I really appreciate the help
Hi @rixmcx59
If the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly. If not, please point it out.
Looking forward to your feedback.
Best Regards,
Henry
User | Count |
---|---|
93 | |
83 | |
77 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |