Issue with SUMX in ISINSCOPE

[avanderschilden]

Hello,

I have a measure that calculates reactivated users by date, and this produces the expected result.

However, I am not able to produce the sum of reactivated users for all days within the selected month.

When I manually add up all reactivated users for a month, it does not match with the result produced on monthly level by the measure. 

Does anyone have an idea why the monthly total is producing a different result from adding the daily numbers up manually?

Regards,

Adrian

Reactivated = 

VAR __CurrentUsers =
    CALCULATETABLE ( VALUES ( 'F Orders'[UserId] ), 'D Order'[IsCompleted] = "Yes" )

VAR __UsersLast180Days =
    CALCULATETABLE (
        VALUES ( 'F Orders'[UserId] ),
        FILTER (
            ALL ( 'D Calendar'[Date] ),
            'D Calendar'[Date]
                >= MIN ( 'D Calendar'[Date] ) - 180
                && 'D Calendar'[Date]
                    <= MIN ( 'D Calendar'[Date] ) - 1
        ),
        'D Order'[IsCompleted] = "Yes"
    )

VAR __UserBefore180Days =
    CALCULATETABLE (
        VALUES ( 'F Orders'[UserId] ),
        FILTER (
            ALL ( 'D Calendar'[Date] ),
            'D Calendar'[Date]
                < MIN ( 'D Calendar'[Date] ) - 180
        ),
        'D Order'[IsCompleted] = "Yes"
    )

VAR __Result =
    CALCULATE (
        DISTINCTCOUNT ( 'F Orders'[UserId] ),
        FILTER (
            ALL ( 'F Orders'[UserId] ),
            'F Orders'[UserId] IN __CurrentUsers
                && 'F Orders'[UserId] IN __UserBefore180Days
                && NOT 'F Orders'[UserId] IN __UsersLast180Days
        )
    )

RETURN
    IF (
        ISINSCOPE ( 'D Calendar'[Date] ),
        __Result,
        SUMX (
            VALUES ( 'D Calendar'[Date] ),
            CALCULATE (
                CALCULATE (
                    DISTINCTCOUNT ( 'F Orders'[UserId] ),
                    FILTER (
                        ALL ( 'F Orders'[UserId] ),
                        'F Orders'[UserId]
                            IN CALCULATETABLE ( VALUES ( 'F Orders'[UserId] ), 'D Order'[IsCompleted] = "Yes" )
                            && 'F Orders'[UserId]
                                IN CALCULATETABLE (
                                    VALUES ( 'F Orders'[UserId] ),
                                    FILTER (
                                        ALL ( 'D Calendar'[Date] ),
                                        'D Calendar'[Date]
                                            < MIN ( 'D Calendar'[Date] ) - 180
                                    ),
                                    'D Order'[IsCompleted] = "Yes"
                                )
                            && NOT 'F Orders'[UserId]
                                IN CALCULATETABLE (
                                    VALUES ( 'F Orders'[UserId] ),
                                    FILTER (
                                        ALL ( 'D Calendar'[Date] ),
                                        'D Calendar'[Date]
                                            >= MIN ( 'D Calendar'[Date] ) - 180
                                            && 'D Calendar'[Date]
                                                <= MIN ( 'D Calendar'[Date] ) - 1
                                    ),
                                    'D Order'[IsCompleted] = "Yes"
                                )
                    )
                )
            )
        )
    )

[Anonymous]

Messy code like hell... 😞 Very likely it can be significantly optimized and shortened.

But I don't think anyone's gonna help you unless you provide some sample data, the model structure and show what you expect and what the actual outcome is.

It would also be useful if you could tell in your own words what the code is actually supposed to do. For instance, why is there a different formula for when one date is in scope and for when there are more than 1? Can this not be unified into one?

Best
Darek

[avanderschilden]

@Anonymous
Did you had time to look at the pbix by any chance?

[avanderschilden]

Hi Darek,

I've created the model and a report page to explain the issue; https://we.tl/t-3M2uO84dhh

The measure "Reactivated Users" is calculating the number of unique users that places an order on a selected date, but not placed an order in the last 40 days, but did place an order before 40 days ago. Therefor, 3 sets are prepared in variables to find if a user is in two of those sets and not in the set of orders in the last 40 days. 

Basically I want the month total in the table to be equal to the manual sum of all days within the month. Somehow the measure is giving a result that is not equal to the total of all days.

Thanks in advance for your help!

Regards,

Adrian