DAX Measure or Calculated Column from Slicer Value

Question

I have a table with customers' SSN, account number, purchase date, and max purchase date (the most recent purchase date for SSN, across all the accounts). Customers can have multiple accounts. I know how to create a measure the calculate the distinct count of all the accounts that haven't been active since a certain date (6 months, 18 month, 24 months).. I would like to create a measure or a calculated column to give me the following information. when users select the date from the slicer (say 6 months) the chart shows the count of the accounts that have not made a purchase in 6 months, the users also want to have a drop down slicer("Yes", "No") to indicate if the SSN had activities under other accounts. i.e. if the max purchase date is greater than the value from the date slicer.

the table structure looks like this: SSN AccountNumber LastPurchaseDate MaxPurchaseDate 123-45-5678 9876 8/2/2018 9/4/2019 123-45-5678 6398 9/4/2019 9/4/2019 135-65-4321 2233 6/6/2019 6/6/2019

Strawberryshrub Strawberryshrub · Accepted Answer · 2019-11-06T05:23:43

Best way here would be if you add a custom column with the time difference (in the query designer):

= [MaxPurchaseDate] - [LastPurchaseDate]

Now you have something like this:

SSN         AccountNumber  LastPurchaseDate   MaxPurchaseDate   DateDiffDays
123-45-5678 9876          9/2/2018            9/4/2019            2
123-45-5678 6398          9/4/2019            9/4/2019            0
135-65-4321 2233          6/12/2019            6/6/2019           6

You can add another column which acts as filter for your 6 months, 18 month, 24 months (convert the DateDiffDays into months).

The following measure counts the accounts:

=Distinctcount('YourTable'[AccountNumber])

If you filter now by your 6 months, 18 month, 24 months column the measure gets after every filtering calculated again and you get your result.

DAX Measure or Calculated Column from Slicer Value

1 Answers