Feb 20 2022 06:30 AM
Hello forum,
I'm a novice at Power Pivot/DAX and I'm trying to think of how I could set a measure to count the number of "recent customers," ie, customers who have conducted a transaction within 12 months with a table set up like this:
- Customer Name
- Transaction Date
- Transaction Category
- Transaction ID
- Sales Person
But the catch is that I want to count in the context of individual sales people, so if there are 50 clients and 10 sales people, the maximum would be 500 recent customers (the double counting doesn't matter). Any advice how to get this type of iteration? Thank you.
Feb 20 2022 08:22 AM
First create Calendar / Date table in data model. You may create directly in Power Pivot (Design -> Date table -> New) or using Power Query (lot of templates on web). If the latest don't forget to mark it as date table.
Create relationship Calendar[Date] with your table on Transaction Date (it will be one to many)
Create measure as
Number of Recent Customers:=CALCULATE (
DISTINCTCOUNT ( Table1[Customer Name] ),
DATESBETWEEN ( 'Calendar'[Date], EDATE ( TODAY (), -12 ), TODAY () )
)
Create PivotTable with sales Person in rows and above measure in values.
Feb 20 2022 02:36 PM - edited Feb 20 2022 06:41 PM
Thanks Sergei. Is there any way to approach this without creating a pivot table and putting sales persons in rows? Not really interested in summarising by sales person. I'm hoping to have the grand total value (i.e sum of the counted values for each sales person) to be charted as a time series.
Feb 21 2022 01:41 AM
That could be like
with
=UNIQUE(
FILTER(
Table1[Customer Name],
Table1[Transaction Date] >=
EDATE(TODAY(), -12)
)
)