Forum Discussion

willb006's avatar
willb006
Regular Visitor
1 year ago
Solved

DATEADD using EARLIER function

Hello, I am trying to get a solve for adding a date in this list using the previous date plus the days listed in the day column.  I tried the earlier function but it didn't work.  Can someone explai...
  • Anonymous's avatar
    Anonymous
    1 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.