Search code examples
powerpivotdax

DAX - Need column with row count within past year

I have a table with sales information at the transaction level. We want to institute a new model where we compensate sales reps if a customer has been makes a purchase after more than a year of dormancy. To figure out how much this would have cost historically, I want to add a column with a flag for whether or not each purchase was the Buyer's first in the past 365 days. What I'd like to do is a rowcount in Powerpivot, for all sales made by that customer in the past 365 days, and wrap it in an IF to set the result to 0 or 1.

Example:

Order Date  Buyer   First Purchase in Year?
1/1/2015    1   1
1/2/2015    2   1
2/1/2015    1   0
4/1/2015    2   0
3/1/2016    2   1
5/1/2017    2   1

Any assistance would be greatly appreciated.

Solution

  • Excellent business use case! It's quite relevant in the business world.

    To break this down for you, I will create 3 columns: 2 with some calculations, and 1 with the result. Once you understood how I did this, you can combine all 3 column formulas and make a single column for your dataset, if you like.

    Here's a picture of the results: Data

    So here's the 3 columns that I created:

    1. Last Purchase - in order to run this calculation, you need to know when the buyer made their last purchase.

      CALCULATE(MAX([Order Date]),FILTER(Table1,[Order Date]<EARLIER([Order Date]) && [Buyer]=EARLIER([Buyer])))

    2. Days Since Last Purchase - now you can compare the Last Purchase date to the current Order Date.

      DATEDIFF([Last Purchase],[Order Date],DAY)

    3. First Purchase in 1 Year - finally, the results column. This simply checks to see if it has been more than 365 days since the last purchase OR if the last purchase column is blank (which means it was the first purchase), and creates the flag you want.

      IF([Days Since Last Purchase]>365 || ISBLANK([Days Since Last Purchase]),1,0)

    Now, you can easily combine the logic of these 3 columns into a single column and get what you want. Hope this helps!

    One note I wanted to add is that for this type of analysis it's not a wise move to do row counts as you had originally suggested, as your dataset can easily expand later on (what if you wanted to add more attribute columns?) and then you would have problems. So this solution that I shared with you is much more robust.