Measure with Summarize or GroupBy with Calculation and Filter??

[dbrandone]

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 the form of a percentage. The denominator is easy to determine, but the numerator is the issue. 

User Story: I select, from a drop down, the year in which customers had their first purchase/order. From those customers, I can enter whether I want to evaluate the repeat customer percentage over 24 months (or 36 months, or X amount of months) from beginning of first purchase year (Jan. 1, "Selected year").

Step 1: Determine all customers that had their first completed purchase within a selected year(Date Range 1 - First Purchase Static Date), and count the number of customers (distinctcount of customers with first purchase in selected year. This has already been done and tested)

Step 2: Take that same customer set from step 1, and then calculate how many completed purchases they had throughout a second date range(Date Range 2 - Purchase Date) that is determined by the report viewer via a numeric parameter in months. (I have already taken that parameter and based on the number entered, determined the MinDate and MaxDate in separate measures that can be leveraged for DatesBetween)

Step 3: With only the dynamic CustomerID's from the customer set in Step 1 in one column and the Total Orders within a dynamic date range from Step 2, I then need to filter the combined dataset to get the amount of customers that had 2 or more orders.

The current measure that I have written for the Numerator is:

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

VAR _TotalOrders = CALCULATE(
                    COUNTROWS(Orders),
                    USERELATIONSHIP(Orders[CreatedDate], 'Date'[Date]),
                    REMOVEFILTERS('Date'[Year]),
                    ALLEXCEPT(Orders, Orders[CustomerUID]),
                    DATESBETWEEN('Date'[Date], _MFMinDate, [_MFMaxDate),
                    Orders[OrderStatus] = "Completed"
)
                            
VAR _Calc = SUMMARIZE(
                    Orders,
                    Orders(CustomerUID],
                    "TotalCustOrders", _TotalOrders
)
        
RETURN _Calc

I have also tried variations of the Summarize function shown above within a Filter expression, and in the filter expression referencing the [TotalCustOrders] column from the summarize functions and filtering it to customers with orders > 1.

The result I am getting always shows just the denominator number. I have verified by looking through the dataset that the denominator number is the correct number.

[dbrandone]

That worked, thanks for the assistance!

[rajendraongole1 · Accepted solution]

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