Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I need to calculate how many consecutive days the ticket (with a specific case number).
I've found a formula here very similar to what I need, but it is considering the weekends, which I don't want to consider, I want it to follow the sequence:
For example, on 11th July, it is a Monday, so I want the Aging to be 2 and continue the sequence until the next day out of the sequence (in this case on 20th July it is 1 again as it should be).
This is the formula:
Solved! Go to Solution.
Hi @Anonymous ,
Since you did not give a specific table, I had to create my own table for testing according to your description, please point out if there are any problems.
Please try below steps
1. below is my test table
Table:
2. add a column to help calculate
Index_By_CaseNumber =
VAR cur_casenumber = 'Table'[CaseNumber]
VAR tmp =
FILTER ( 'Table', 'Table'[CaseNumber] = cur_casenumber )
RETURN
RANKX ( tmp, [Date],, ASC )
3. add a new calumn to calculate the days
Column =
VAR cur_date = 'Table'[Date]
VAR cur_casnumber = 'Table'[CaseNumber]
VAR cur_index = 'Table'[Index_By_CaseNumber]
VAR pre_date =
CALCULATE (
SELECTEDVALUE ( 'Table'[Date] ),
'Table'[CaseNumber] = cur_casnumber,
'Table'[Index_By_CaseNumber] = cur_index - 1,
ALL ( 'Table' )
)
VAR diff_for_day =
DATEDIFF ( pre_date, cur_date, DAY )
RETURN
SWITCH ( diff_for_day, BLANK (), cur_index, 1, cur_index )
Please refer the attached .pbix file.
Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
Since you did not give a specific table, I had to create my own table for testing according to your description, please point out if there are any problems.
Please try below steps
1. below is my test table
Table:
2. add a column to help calculate
Index_By_CaseNumber =
VAR cur_casenumber = 'Table'[CaseNumber]
VAR tmp =
FILTER ( 'Table', 'Table'[CaseNumber] = cur_casenumber )
RETURN
RANKX ( tmp, [Date],, ASC )
3. add a new calumn to calculate the days
Column =
VAR cur_date = 'Table'[Date]
VAR cur_casnumber = 'Table'[CaseNumber]
VAR cur_index = 'Table'[Index_By_CaseNumber]
VAR pre_date =
CALCULATE (
SELECTEDVALUE ( 'Table'[Date] ),
'Table'[CaseNumber] = cur_casnumber,
'Table'[Index_By_CaseNumber] = cur_index - 1,
ALL ( 'Table' )
)
VAR diff_for_day =
DATEDIFF ( pre_date, cur_date, DAY )
RETURN
SWITCH ( diff_for_day, BLANK (), cur_index, 1, cur_index )
Please refer the attached .pbix file.
Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |