Problems with measuring purchases in period prior to date filter

[KeeganM4]

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

[KeeganM4 · Accepted solution]

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

[Anonymous]

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.

[KeeganM4]

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

[lbendlin]

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-...

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/1447...

[KeeganM4]

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

Here is a link to some sample data, thanks!