CODEWITHSUNDEEP
powerbidaxmeasure
Elle
Elle

Reputation: 25

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

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

Upvotes: 2

Views: 6364

Answers (2)

Jody Highroller
Jody Highroller

Reputation: 1029

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)

Upvotes: 1

teylyn
teylyn

Reputation: 35900

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.

Upvotes: 0

Related Questions