cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
dcruise Frequent Visitor
Frequent Visitor

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 Period is June 30). In order for a customer to be active, they have to have had a purchase in the LAST 80 DAYS of that Invoice Period. (i.e. June 30 - 80 days = April 11.). I need to calculate UNIQUE Customers in the Rolling Time period of Each Period (month). 

 

Date Ranges:

range.PNG

 

Things I have tried:

Active Customers=
CALCULATE (
DISTINCTCOUNT(Sales[StoreLicense]),
DATESBETWEEN(Sales[InvoicePeriod],MAX(Sales[InvoicePeriod])-80,MAX(Sales[InvoicePeriod]))
)

 

Very specific date range:

Active Stores2 =
CALCULATE(DISTINCTCOUNT(Sales[StoreLicense]),DATESBETWEEN(CalendarPeriods[End of Month],"4/11/2018","7/31/2018"))

 

 

Measure 14 = CALCULATE (
DISTINCTCOUNT( Sales[StoreLicense] ),
ALLEXCEPT( Sales,Sales[InvoicePeriod],Sales[Brand],Sales[TxnType] ),
AND (
Sales[InvoiceDate] >= DATE ( 2018,04,11),
Sales[InvoiceDate] <= DATE ( 2018,07,31)
)
)

 

None of them return the correct answer. 

I believe what is happening is that the dates that may "overlap" active account periods, are not being counted in both, just the month the sales is in. Example - and April 7 sale could fall in the active account range (period - 80 days) for April, May, June and July. But it is only being counted in April.

 

 

I have a relationship on dates, (invoice date to Date), if it helps to use a calendar date. 

relationships.PNG

 

 

 

 

 

 

 

 

 

Logic:

IF INVOICE DATE >= INVOICE PERIOD - 80 AND INVOICE DATE <= INVOICE PERIOD, DISTINCT COUNT of LICENSE.

Example:

IF 4/21/2018 >= 6/30/2018-80 (4/11/2018) AND 4/21/2018 <= 6/30/2018 THEN  COUNT OF LICENSE

 

 

Here is an example of what is happening:

image (3).png

233 should be for the 6/30/18 period. But it continues to split them but the account period in which the invoice actually happened.  

 

 

I am trying to provide as much detail as possbile, if there is anything you need that would help, let me know!! 

 


Thank you.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: CALCULATE DISTINCT For Rolling Time Period

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

捕获.PNG 

 

Regards,

Jimmy Tao

View solution in original post

3 REPLIES 3
Community Support Team
Community Support Team

Re: CALCULATE DISTINCT For Rolling Time Period

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

捕获.PNG 

 

Regards,

Jimmy Tao

View solution in original post

Super User
Super User

Re: CALCULATE DISTINCT For Rolling Time Period

Hi,

 

Share the link from where i can download your PBI file.  Also, show your expected result.

dcruise Frequent Visitor
Frequent Visitor

Re: CALCULATE DISTINCT For Rolling Time Period

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

 

 

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 6 members 880 guests
Please welcome our newest community members: