Forum Discussion

dcruise's avatar
dcruise
Frequent Visitor
8 years ago
Solved

CALCULATE DISTINCT For Rolling Time Period

I have a sales dataset of a bunch of customer transactions. We are breaking these transactions by Invoice Period which is essentially the last day of the month. (i.e. Invoice Date is June 7, Invoice ...
Need Help
Tips and Tricks
Tutorial Requests
  • v-yuta-msft's avatar
    v-yuta-msft
    8 years ago

    Hi dcruise,

     

    You should use all() to remove the previous filter context, modify you measure using DAX like pattern below and check if it can meet your requirement:

    Active Customers = 
CALCULATE (
    DISTINCTCOUNT ( Sales[StoreLicense] ),
    FILTER (
        ALL ( Sales ),
        Sales[InvoicePeriod]
            >= MAX ( Sales[InvoicePeriod] ) - 80
            && Sales[InvoicePeriod] <= MAX ( Sales[InvoicePeriod] )
    )
)

     

     

    Regards,

    Jimmy Tao

v-yuta-msft's avatar
v-yuta-msft
Icon for Community Support rankCommunity Support
8 years ago

Hi dcruise,

 

You should use all() to remove the previous filter context, modify you measure using DAX like pattern below and check if it can meet your requirement:

Active Customers = 
CALCULATE (
    DISTINCTCOUNT ( Sales[StoreLicense] ),
    FILTER (
        ALL ( Sales ),
        Sales[InvoicePeriod]
            >= MAX ( Sales[InvoicePeriod] ) - 80
            && Sales[InvoicePeriod] <= MAX ( Sales[InvoicePeriod] )
    )
)

 

 

Regards,

Jimmy Tao

dcruise's avatar
dcruise
Frequent Visitor
8 years ago

With a couple tweaks, I think this will do it! Thank you so much.