Search code examples
powerbidaxmeasure

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


Solution

  • 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)