Forum Discussion

chaitanya1's avatar
chaitanya1
Helper I
1 year ago
Solved

in data view in visual view numbers difference

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?

Data Protection
Need Help
Show and Tell
Tips and Tricks
  • chaitanya1's avatar
    chaitanya1
    1 year ago

    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.

2 Replies

  • wardy912's avatar
    wardy912
    Super User
    1 year ago

    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's avatar
      chaitanya1
      Helper I
      1 year ago

      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.