PowerBI DAX Measure To give a distinct count in last 30 days

user_17328107 0
Viewed 1608

I'm new to PowerBI/DAX and having trouble figure out how to create a measure that gives me a distinct count of customers that had a signed form within the last 30 days. I have a customer ID field (Cust_ID), form signed date (FormSignDTS). This is what I tried...

Last 30 Days = 
   VAR _TodaysDate = Today()
   VAR _StartDate = dateadd (Cutomer[FormSignedDTS], -30, Day)
  return 
  calculate (distinctcount (Customer[CustID]), keepfilters (Customer[FormSignedDTS] >= _StartDate))

But it get the following error... "a date column containing duplicate dates was specified in the call to function dateadd"

Any help I receive on this will be greatly appreciated! Thanks

dax powerbi measure
2 Answers

You created the variable TodaysDate, but never used it anywhere within your measure.

This will give you a distinct count of every CustID within your table...

Distinct Count of Customers:=DISTINCTCOUNT(Customer[CustID])

But you only want customers within the last 30 days. So, we just need to figure out what today minus 30 is, and then filter to items newer than that.

Last 30 Days:=VAR startDate = Today() - 30

RETURN

CALCULATE(DISTINCTCOUNT(Customer[CustID]),Customer[FormSignedDTS]>=startDate)
user_6121630

if you want a distinct count of customers of the last 30 days, you don't manipulate the signed date. Rather, you calculate a distinct count of the table where the signed date is greater than Today() minus 30 days.

So, change the variable StartDate to refer to Today minus 30 days and use that in your filter.

user_4479700
Related