in data view in visual view numbers difference

[chaitanya1]

This is terminations showing correct number:Terminated Customers EOM =

VAR MonthEnd =

    EOMONTH ( MAX ( 'DimDate'[Date] ), 0 )

VAR TermCount =

    CALCULATE (

        DISTINCTCOUNT ( 'V1+V2+Terminations CitiesOrders'[Service Ref] ),

        'V1+V2+Terminations CitiesOrders'[New OrderStatus] = "Terminated",

        FILTER (

            ALL ( 'V1+V2+Terminations CitiesOrders' ),

            'V1+V2+Terminations CitiesOrders'[endDate] <= MonthEnd

        )

    )

VAR LatestMonth =

    EOMONTH (

        MAXX ( ALL ( 'V1+V2+Terminations CitiesOrders' ), 'V1+V2+Terminations CitiesOrders'[endDate] ),

        0

    )

RETURN

IF (

    MonthEnd <= LatestMonth,

    TermCount,

    BLANK()

)

this is :Active Customers EOM =

VAR MonthEnd =

    EOMONTH ( MAX ( 'DimDate'[Date] ), 0 )

VAR TermCount =

    CALCULATE (

        DISTINCTCOUNT ( 'V1+V2+Terminations CitiesOrders'[Service Ref] ),

        'V1+V2+Terminations CitiesOrders'[New OrderStatus] = "Active",

        FILTER (

            ALL ( 'V1+V2+Terminations CitiesOrders' ),

            'V1+V2+Terminations CitiesOrders'[startDate] <= MonthEnd

        )

    )

VAR LatestMonth =

    EOMONTH (

        MAXX ( ALL ( 'V1+V2+Terminations CitiesOrders' ), 'V1+V2+Terminations CitiesOrders'[startDate] ),

        0

    )

RETURN

IF (

    MonthEnd <= LatestMonth,

    TermCount,

    BLANK()

)

For the terminations data, both the Data view and the visuals are showing the correct numbers.
But for active customers, the Data view and the visuals don’t match.

Example: On July, the Data view shows 7,645, but the visual shows 6,880, based on the measure.

It seems to be a filter context issue — I can’t align the numbers between the Data view and the visuals for active customers. Can you help me fix this?

[wardy912]

Hi @chaitanya1 

 Your terminated customers users this

FILTER (
    ALL ( 'V1+V2+Terminations CitiesOrders' ),
    'V1+V2+Terminations CitiesOrders'[endDate] <= MonthEnd
)

This removes all filters from the table and applies a custom filter based on endDate. So it behaves consistently across visuals and the Data view

Active customers uses

FILTER (
    ALL ( 'V1+V2+Terminations CitiesOrders' ),
    'V1+V2+Terminations CitiesOrders'[startDate] <= MonthEnd
)

This also removes filters, but only considers startDate. If your data model or visuals apply filters on endDate, New OrderStatus, or other fields, they might interfere unless explicitly removed.

Also, if your visual has slicers or filters (e.g., city, product, etc.), they do apply unless removed with ALLSELECTED, REMOVEFILTERS, or ALL.

Use this modified DAX for Active users

Active Customers EOM =
VAR MonthEnd = EOMONTH(MAX('DimDate'[Date]), 0)
VAR ActiveCount =
    CALCULATE(
        DISTINCTCOUNT('V1+V2+Terminations CitiesOrders'[Service Ref]),
        'V1+V2+Terminations CitiesOrders'[New OrderStatus] = "Active",
        FILTER(
            ALL('V1+V2+Terminations CitiesOrders'),
            'V1+V2+Terminations CitiesOrders'[startDate] <= MonthEnd &&
            (
                ISBLANK('V1+V2+Terminations CitiesOrders'[endDate]) ||
                'V1+V2+Terminations CitiesOrders'[endDate] > MonthEnd
            )
        )
    )
VAR LatestMonth =
    EOMONTH(
        MAXX(ALL('V1+V2+Terminations CitiesOrders'), 'V1+V2+Terminations CitiesOrders'[startDate]),
        0
    )
RETURN
IF(MonthEnd <= LatestMonth, ActiveCount, BLANK())

I hope this helps, please give kudos and mark as solved if it does, thanks!

[chaitanya1 · Accepted solution]

Thanks @wardy912 your version didn't worked for me, based on my requirement i followed other way i tried thi below.

Active Customers EOM =

CALCULATE (

    DISTINCTCOUNT ( 'V1+V2+Terminations CitiesOrders'[Service Ref] ),

    FILTER(

        'DimDate',

        'DimDate'[Date] <= MAX('V1+V2+Terminations CitiesOrders'[startDate])

    )

)

Active Customers Cumulative =

CALCULATE(

    [Active Customers EOM],

    FILTER(

        ALL('DimDate'),

        'DimDate'[Date] <= MAX('DimDate'[Date]) &&

        'DimDate'[Date] <= MAX('V1+V2+Terminations CitiesOrders'[startDate])

    )

)

like this leave terminations terminations measure as is and to this i added cummulation to this and i got result.