Other dax formula for Months with Purchase

[Easley08]

Hi,

is there another formula with this 

Months w Purchase = CALCULATE(

    DISTINCTCOUNT(Sales[Purchase Date].[Month]),

    FILTER(Sales, NOT(ISBLANK(Sales[Transactions])) ))

im getting the result that i want if the table is in by year and month

RESULT = CALCULATE(DIVIDE([Transaction Count],[Months w Purchase],0),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-12,MONTH))

but if i add it with Customer email it crash and the loading time is so slow

Thanks

[v-hashadapu]

Hi @Easley08 , Please let us know if your issue is solved. If it is, consider marking the answer that helped 'Accept as Solution', so others with similar queries can find it easily. If not, please share the details.
Thank you.

[v-hashadapu]

Hi @Easley08 , Please let us know if your issue is solved. If it is, consider marking the answer that helped 'Accept as Solution', so others with similar queries can find it easily. If not, please share the details.
Thank you.

[v-hashadapu]

Hi @Easley08 , Please let us know if your issue is solved. If it is, consider marking the answer that helped 'Accept as Solution', so others with similar queries can find it easily. If not, please share the details.
Thank you.

[Easley08]

Hi sir, 

currently the summarize table works fine but can you show also to get only running total of 1year from March 2025 to Apr 2024 only seems the datesinperiod not working. 

here is the expected value 

456 total from Apr 2024 - Mar 2025 

456 / 12 = 38. but currently im getting 85

[v-hashadapu]

Hi @Easley08 , Thank you for reaching out to the Microsoft Community Forum.

The issue with your current measure yielding 85 stems from an incorrect calculation of MonthsWithPurchase and potential date range mismatches.

Please try below solutions:

1. Use DATESBETWEEN to strictly enforce the date range from April 2024 to March 2025, ensuring the calculation only includes this period. It calculates the TransactionCount directly from the CustomerSummary table and computes MonthsWithPurchase by counting distinct months with purchases in the Sales table within the specified range. This approach leverages the pre-aggregated CustomerSummary table for transactions and avoids unnecessary recalculations.

Result =

VAR StartDate = DATE(2024, 4, 1)  -- April 1, 2024

VAR EndDate = DATE(2025, 3, 31)   -- March 31, 2025

VAR TransactionCount =

    CALCULATE(

        SUM(CustomerSummary[TransactionCount]),

        DATESBETWEEN('Date'[Date], StartDate, EndDate)

    )

VAR MonthsWithPurchase =

    CALCULATE(

        DISTINCTCOUNT(Sales[Purchase Date].[Month]),

        DATESBETWEEN('Date'[Date], StartDate, EndDate),

        NOT ISBLANK(Sales[Transactions])

    )

RETURN

DIVIDE(TransactionCount, MonthsWithPurchase, 0)

2. If you prefer a dynamic 12-month period ending at a specific date, this method uses DATESINPERIOD to calculate the running total over the last 12 months from a fixed end date. It ensures the TransactionCount and MonthsWithPurchase are calculated within this dynamic range, maintaining efficiency by using the CustomerSummary table for transactions and directly querying the Sales table for the distinct months with purchases. This is useful if your requirements change to a rolling period.

              Result =

VAR EndDate = DATE(2025, 3, 31)   -- End of March 2025

VAR TransactionCount =

    CALCULATE(

        SUM(CustomerSummary[TransactionCount]),

        DATESINPERIOD('Date'[Date], EndDate, -12, MONTH)

    )

VAR MonthsWithPurchase =

    CALCULATE(

        DISTINCTCOUNT(Sales[Purchase Date].[Month]),

        DATESINPERIOD('Date'[Date], EndDate, -12, MONTH),

        NOT ISBLANK(Sales[Transactions])

    )

RETURN

DIVIDE(TransactionCount, MonthsWithPurchase, 0)

Both methods ensure TransactionCount sums to 456 for the period and calculate MonthsWithPurchase as 12 by counting distinct months with purchases in the specified range, yielding 456 / 12 = 38. They avoid the pitfall of using the precomputed MonthsWithPurchase from CustomerSummary, which was causing the incorrect result of 85.

If this helped solve the issue, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.

[bhanu_gautam]

@Easley08 Create a calculated column for the number of months with purchases per customer

MonthsWithPurchasePerCustomer =
CALCULATE(
DISTINCTCOUNT(Sales[Purchase Date].[Month]),
FILTER(Sales, NOT(ISBLANK(Sales[Transactions]))),
ALLEXCEPT(Sales, Sales[Customer Email])
)

 Create a measure to calculate the result

DAX
Result =
VAR MaxDate = MAX('Date'[Date])
VAR StartDate = EDATE(MaxDate, -12)
VAR TransactionCount = [Transaction Count]
VAR MonthsWithPurchase =
CALCULATE(
MAX(Sales[MonthsWithPurchasePerCustomer]),
DATESINPERIOD('Date'[Date], MaxDate, -12, MONTH)
)
RETURN
DIVIDE(TransactionCount, MonthsWithPurchase, 0)

ow, when you add the Customer email to your table, the performance should be better because the number of months with purchases is pre-calculated per customer.

If you still experience performance issues, consider creating a summary table that aggregates the data at the customer level and use that table for your calculations. This can further reduce the amount of data being processed in real-time.

DAX
CustomerSummary =
SUMMARIZE(
Sales,
Sales[Customer Email],
"MonthsWithPurchase", CALCULATE(
DISTINCTCOUNT(Sales[Purchase Date].[Month]),
FILTER(Sales, NOT(ISBLANK(Sales[Transactions])))
),
"TransactionCount", SUM(Sales[Transactions])
)

Then, you can create your measure based on this summary table:

DAX
Result =
VAR MaxDate = MAX('Date'[Date])
VAR StartDate = EDATE(MaxDate, -12)
VAR TransactionCount =
CALCULATE(
SUM(CustomerSummary[TransactionCount]),
DATESINPERIOD('Date'[Date], MaxDate, -12, MONTH)
)
VAR MonthsWithPurchase =
CALCULATE(
MAX(CustomerSummary[MonthsWithPurchase]),
DATESINPERIOD('Date'[Date], MaxDate, -12, MONTH)
)
RETURN
DIVIDE(TransactionCount, MonthsWithPurchase, 0)