Power Pivot - iterating count measure?

Copper Contributor

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. 

 

 

3 Replies

@JXHK_22 

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.

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.

@JXHK_22 

That could be like

image.png

with

=UNIQUE(
    FILTER(
        Table1[Customer Name],
        Table1[Transaction Date] >=
            EDATE(TODAY(), -12)
    )
)