Forum Discussion
DATEADD using EARLIER function
- Anonymous1 year ago
Hi,willb006 ,thank you for your reply.
According to your latest information, you need to realize the effect of grouping calculation based on TicketID on the original basis, then you just need to change all the places in the code where ALL('Table') is used to ALLEXCEPT('Table'['ticket_number']), and let the original scope of the filter table which removes all the external filters be changed to just affected by the grouping of ['ticket_number'].
to achieve the effect of grouping (each ticket_number is counted internally).
like this:result_column02 = VAR _date = [comment_sort] VAR _numnotbalnk = CALCULATE ( MAX ( 'Table'[comment_sort] ), FILTER ( ALLEXCEPT ( 'Table', 'Table'[ticket_number] ), 'Table'[comment_sort] < _date && NOT ( ISBLANK ( [wall_commentcreatedon] ) ) ) ) VAR diffday = CALCULATE ( SUM ( 'Table'[Days] ), FILTER ( ALLEXCEPT ( 'Table', 'Table'[ticket_number] ), 'Table'[comment_sort] > _numnotbalnk && 'Table'[comment_sort] <= _date ) ) VAR lastdate1 = CALCULATE ( MAX ( 'Table'[wall_commentcreatedon] ), FILTER ( ALLEXCEPT ( 'Table', 'Table'[ticket_number] ), 'Table'[comment_sort] = _numnotbalnk ) ) RETURN IF ( ISBLANK ( 'Table'[wall_commentcreatedon] ), lastdate1 + diffday, 'Table'[wall_commentcreatedon] )Note that your data is quite specific and implements not really iterative logic (it looks like iteration, but it is not).
And fortunately, the source you gave has a comment_sort column to achieve an iteration-like effect (actually a conditional judgment based on sortnum, where each row is recalculated from the first row). If you don't provide a column for comment_sort, everything will get very tricky. (DAX itself doesn't support implementing iteration (it calculates uniformly based on a whole column, not on each cell, and there is no looping logic), and if the implementation wants true iteration logic, it's usually handled locally in the datasource or through Power Query using looping functions.
I hope my explanation can answer your questions.
I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Sure. Here is an excel document with the expected output in the last column
| ticket_number | wall_comment | wall_commentcreatedon | comment_sort | Days | Expected Output |
| 6212482 | PENDING (CUST) PA REQUEST SUBMISSION - Assigned. Assigned Role - Licensee. | 6/8/2024 0:00 | 1 | 10 | 6/8/2024 |
| 6212482 | PENDING SURVEY - Assigned. Assigned Role - Owner. | 6/17/2024 0:00 | 2 | 40 | 6/17/2024 |
| 6212482 | PENDING (CUST) REVIEW - Assigned. Assigned Role - Licensee. | 9/4/2024 0:00 | 3 | 10 | 9/4/2024 |
| 6212482 | PENDING (DOM) REVIEW - Assigned. Assigned Role - Owner. | 4 | 10 | 9/14/2024 | |
| 6212482 | PENDING (DOM) DESIGN - Assigned. Assigned Role - Owner. | 9/6/2024 0:00 | 5 | 10 | 9/6/2024 |
| 6212482 | PENDING (CUST) PAYMENT - Assigned. Assigned Role - Owner. | 6 | 10 | 9/16/2024 | |
| 6212482 | PENDING Make-Ready - Assigned. Assigned Role - Owner. | 7 | 135 | 1/29/2025 |
Hello,Kaviraj11 ,thanks for your concern about this issue.
And I would like to share some additional solutions below.
Hi,willb006 .I am glad to help you.
I have written the following code, and the end result is the same as your expected columns.
The Dax code(calculate column):
result_column02 =
VAR _date = [comment_sort]
VAR _numnotbalnk =
CALCULATE (
MAX ( 'Table'[comment_sort] ),
FILTER (
ALL ( 'Table' ),
'Table'[comment_sort] < _date
&& NOT ( ISBLANK ( [wall_commentcreatedon] ) )
)
)
VAR diffday =
CALCULATE (
SUM ( 'Table'[Days] ),
FILTER (
ALL ( 'Table' ),
'Table'[comment_sort] > _numnotbalnk
&& 'Table'[comment_sort] <= _date
)
)
VAR lastdate1 =
CALCULATE (
MAX ( 'Table'[wall_commentcreatedon] ),
FILTER ( ALL ( 'Table' ), 'Table'[comment_sort] = _numnotbalnk )
)
RETURN
IF (
ISBLANK ( 'Table'[wall_commentcreatedon] ),
lastdate1 + diffday,
'Table'[wall_commentcreatedon]
)
here is my test data:
Note that the effect of iterative looping is not possible with DAX functions in power BI Desktop (if you want to achieve this, it is generally recommended that you solve it at the data source or use a looping function through Power Query)
This is actually one of the flaws in Power BI DAX (because Power BI DAX code performs the same operation based on a whole column. Whereas excel can do this easily because it can use each cell as a unit of calculation)
Fortunately, your requirement implementation is not really iterative, so it can be achieved with dax code similar to iteration
I hope my test will help you.
I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- willb0061 year agoRegular Visitor
v-jtian-msft thank you for this. Would it be iterative if I had more than one Ticket number? I tried your solution and I had to do it like this "
result_column02 =VAR _date = SUM(Append1[comment_sort])VAR _numnotbalnk =CALCULATE (SUM('Append1'[comment_sort] ),FILTER (ALL ( 'Append1'),'Append1'[comment_sort] < _date&& NOT ( ISBLANK ( [wall_commentcreatedon] ) )))VAR diffday =CALCULATE (SUM ( 'Append1'[Days] ),FILTER (ALL ( 'Append1' ),'Append1'[comment_sort] > _numnotbalnk&& 'Append1'[comment_sort] <= _date))VAR lastdate1 =CALCULATE (MAX ( 'Append1'[wall_commentcreatedon] ),FILTER ( ALL ( 'Append1' ), 'Append1'[comment_sort] = _numnotbalnk ))RETURNIF (ISBLANK ( max('Append1'[wall_commentcreatedon] )),lastdate1 + diffday,max('Append1'[wall_commentcreatedon]))" Here is the data set with more than one ticketticket_number wall_comment wall_commentcreatedon comment_sort Days Expected Output 6212482 PENDING (CUST) PA REQUEST SUBMISSION - Assigned.
Assigned Role - Licensee.6/8/2024 0:00 1 10 6/8/2024 6212482 PENDING SURVEY - Assigned.
Assigned Role - Owner.6/17/2024 0:00 2 40 6/17/2024 6212482 PENDING (CUST) REVIEW - Assigned.
Assigned Role - Licensee.9/4/2024 0:00 3 10 9/4/2024 6212482 PENDING (DOM) REVIEW - Assigned.
Assigned Role - Owner.4 10 9/14/2024 6212482 PENDING (DOM) DESIGN - Assigned.
Assigned Role - Owner.9/6/2024 0:00 5 10 9/6/2024 6212482 PENDING (CUST) PAYMENT - Assigned.
Assigned Role - Owner.6 10 9/16/2024 6212482 PENDING Make-Ready - Assigned.
Assigned Role - Owner.7 135 1/29/2025 4979490 PENDING (CUST) PA REQUEST SUBMISSION - Assigned.
Assigned Role - Licensee.1/26/2022 0:00 1 10 1/26/2022 4979490 PENDING SURVEY - Assigned.
Assigned Role - Owner.5/8/2023 0:00 2 40 5/8/2023 4979490 PENDING (CUST) REVIEW - Assigned.
Assigned Role - Licensee.3 10 6/17/2023 4979490 PENDING (DOM) REVIEW - Assigned.
Assigned Role - Owner.4 10 6/27/2023 4979490 PENDING (DOM) DESIGN - Assigned.
Assigned Role - Owner.5 10 7/7/2023 4979490 PENDING (CUST) PAYMENT - Assigned.
Assigned Role - Owner.6 10 7/17/2023 4979490 PENDING Make-Ready - Assigned.
Assigned Role - Owner.5/8/2023 0:00 7 135 5/8/2023 4979490 PENDING GRANTED - Assigned.
Assigned Role - Owner.10/11/2023 0:00 8 1 10/11/2023 4979490 PENDING (CUST) PA WORK COMPLETE - Assigned.
Assigned Role - Licensee.10/11/2023 0:00 9 1 10/11/2023 4979490 PENDING VALIDATE - Assigned.
Assigned Role - Owner.8/26/2024 0:00 10 1 8/26/2024 4979490 PENDING (CUST) VIOLATION REPAIR - Assigned.
Assigned Role - Licensee.9/5/2024 0:00 11 1 9/5/2024 - Anonymous1 year agoNot applicable
Hi,willb006 ,thank you for your reply.
According to your latest information, you need to realize the effect of grouping calculation based on TicketID on the original basis, then you just need to change all the places in the code where ALL('Table') is used to ALLEXCEPT('Table'['ticket_number']), and let the original scope of the filter table which removes all the external filters be changed to just affected by the grouping of ['ticket_number'].
to achieve the effect of grouping (each ticket_number is counted internally).
like this:result_column02 = VAR _date = [comment_sort] VAR _numnotbalnk = CALCULATE ( MAX ( 'Table'[comment_sort] ), FILTER ( ALLEXCEPT ( 'Table', 'Table'[ticket_number] ), 'Table'[comment_sort] < _date && NOT ( ISBLANK ( [wall_commentcreatedon] ) ) ) ) VAR diffday = CALCULATE ( SUM ( 'Table'[Days] ), FILTER ( ALLEXCEPT ( 'Table', 'Table'[ticket_number] ), 'Table'[comment_sort] > _numnotbalnk && 'Table'[comment_sort] <= _date ) ) VAR lastdate1 = CALCULATE ( MAX ( 'Table'[wall_commentcreatedon] ), FILTER ( ALLEXCEPT ( 'Table', 'Table'[ticket_number] ), 'Table'[comment_sort] = _numnotbalnk ) ) RETURN IF ( ISBLANK ( 'Table'[wall_commentcreatedon] ), lastdate1 + diffday, 'Table'[wall_commentcreatedon] )Note that your data is quite specific and implements not really iterative logic (it looks like iteration, but it is not).
And fortunately, the source you gave has a comment_sort column to achieve an iteration-like effect (actually a conditional judgment based on sortnum, where each row is recalculated from the first row). If you don't provide a column for comment_sort, everything will get very tricky. (DAX itself doesn't support implementing iteration (it calculates uniformly based on a whole column, not on each cell, and there is no looping logic), and if the implementation wants true iteration logic, it's usually handled locally in the datasource or through Power Query using looping functions.
I hope my explanation can answer your questions.
I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.- willb0061 year agoRegular Visitor
Makes sense. I was using th all except but only on the last step. Now I am getting this error "A single value for column 'wall_commentcreatedon' in table 'Append1' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result." Append1 is my table name