Forum Discussion

dbrandone's avatar
dbrandone
Icon for Helper IV rankHelper IV
1 year ago
Solved

Measure with Summarize or GroupBy with Calculation and Filter??

I have a report where the customer is needing to apply two different date sets at different points of the calculation along with groupings to determine the final number. The final metric will be in t...
  • rajendraongole1's avatar
    1 year ago

    Hi dbrandone  - can you try the below approach for Numerator. check it and let know

     

    _RepeatNumerator =
    VAR _MFMinDate = [_MonthsForwardMinDate]
    VAR _MFMaxDate = [_MonthsForwardMaxDate]

    -- Step 1: Get the customers with first purchase in the selected year
    VAR _FirstPurchaseCustomers =
    CALCULATETABLE(
    VALUES(Orders[CustomerUID]),
    Orders[FirstPurchaseYear] = SELECTEDVALUE('YearSelection'[Year])
    )

    -- Step 2: Calculate total completed purchases within the selected month range
    VAR _OrdersWithinRange =
    ADDCOLUMNS(
    _FirstPurchaseCustomers,
    "TotalCustOrders",
    CALCULATE(
    COUNTROWS(Orders),
    Orders[OrderStatus] = "Completed",
    DATESBETWEEN('Date'[Date], _MFMinDate, _MFMaxDate),
    KEEPFILTERS(Orders[CustomerUID] IN _FirstPurchaseCustomers)
    )
    )

    -- Step 3: Count customers with at least 2 completed orders
    VAR _RepeatCustomers =
    COUNTROWS(
    FILTER(
    _OrdersWithinRange,
    [TotalCustOrders] >= 2
    )
    )

    RETURN _RepeatCustomers

     

    Hope it works. please check