topic Re: Problems with measuring purchases in period prior to date filter in DAX Commands and Tips

Problems with measuring purchases in period prior to date filter

KeeganM4 — Thu, 13 Jun 2024 03:10:49 GMT

I have an invoice table with columns invoiceID, buyerID, InvoiceDate and another standard Dates table

I am trying to be able track buyer re-engagement by checking the 90 days previous to the start of my date filter. If a buyer has an invoice the filtered window, but had no invoices in the 90 days before the start of the date filter they should appear as reengaged.

My problem is I am getting the same count of buyers as for my count of buyers within the filtered period, and when I modify my measure to check for the buyers only in the 90 days prior to the filter I get zero.

My code:

ReengagedBuyers = VAR MinSelectedDate = CALCULATE(MIN('Date'[Dates]), ALLSELECTED('Date')) VAR StartDate = MinSelectedDate VAR EndDate = CALCULATE(MAX('Date'[Dates]), ALLSELECTED('Date')) VAR Prior90DaysStart = StartDate - 90 RETURN COUNTROWS( FILTER( VALUES(invoice[buyerid]), CALCULATE( COUNTROWS(invoice), invoice[Created Date] >= StartDate && invoice[Created Date] <= EndDate, ALL(invoice[Created Date]) ) > 0 && CALCULATE( COUNTROWS(invoice), invoice[Created Date] >= Prior90DaysStart && invoice[Created Date] < StartDate, ALL(invoice[Created Date]) ) = 0 ) )

Any help would be appreciated, thank you!

Sample data here:
https://drive.google.com/drive/folders/1VK0VJ0XtmnvBHeYKtc1YG5FDmtDIajU3?usp=sharing

Re: Problems with measuring purchases in period prior to date filter

lbendlin — Thu, 13 Jun 2024 00:12:24 GMT

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: Problems with measuring purchases in period prior to date filter

KeeganM4 — Thu, 13 Jun 2024 03:10:31 GMT

https://drive.google.com/drive/folders/1VK0VJ0XtmnvBHeYKtc1YG5FDmtDIajU3?usp=sharing

Here is a link to some sample data, thanks!

Re: Problems with measuring purchases in period prior to date filter

Anonymous — Thu, 13 Jun 2024 06:32:48 GMT

Hi KeeganM4 ,

First of all, many thanks to for your very quick and effective replies.

I can’t open the file link.

Based on my testing, please try the following methods:

1.Create the simple tables.

2.Create the new measure to calculate buyerID.

ReengagedBuyers = VAR MinSelectedDate = MIN('Date'[Date]) VAR StartDate = MinSelectedDate VAR EndDate = MAX('Date'[Date]) VAR Prior90DaysStart = StartDate - 90 RETURN COUNTROWS( FILTER( VALUES('Invoice Table'[BuyerID]), CALCULATE( COUNTROWS('Invoice Table'), 'Invoice Table'[InvoiceDate] >= StartDate && 'Invoice Table'[InvoiceDate] <= EndDate, ALL('Invoice Table'[InvoiceDate]) ) > 0 && CALCULATE( CALCULATE( COUNTROWS('Invoice Table'), 'Invoice Table'[InvoiceDate] >= Prior90DaysStart && 'Invoice Table'[InvoiceDate] < StartDate, ALL('Invoice Table'[InvoiceDate]) ) ) = 0 ) )

3.Drag the Date field into the slicer visual.

4.Drag the measure into the card visual. The result is shown below.

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Problems with measuring purchases in period prior to date filter

KeeganM4 — Thu, 13 Jun 2024 13:46:24 GMT

Thanks for your reply, but I don't think this is solving.

There is a relationship between my invoice dates and my Dates. In your sample file, I have added that relationship and another card to check for unique buyers in range, and I am still getting the same count for both, where I would expect reengaged buyers to only be a portion of the total buyers in period

Re: Problems with measuring purchases in period prior to date filter

KeeganM4 — Thu, 13 Jun 2024 14:34:40 GMT

Update, I have solved the issue. My original metric works, but I needed to add CROSSFILTER to set the relationship of my invoice table with dates table to NONE so that the filter doesn't filter out all the dates prior to my range