Last date of an ocurrence given condition - Power BI

Question

I'm looking for a formula for a COLUMN in Power BI that shows the last date up to "Date" that an specific customer made a purchase. Note that the last date may be variable given the "Date" I want the result.

I have found solutions that will show, for example, last date for costumer 101 = 3/31/2020 for all rows of that costumer. That doesn't work for me since the Date which I need to see the data is variable. So if I have a Slicer for "Date" = 2/28/2020, I want the last date up to THAT POINT that a customer made a purchase (so, in this case, 1/31/2020)

CustomerCode    Date        Purchase    Result 
101             1/31/2020   $50         1/31/2020
102             1/31/2020   $0          null
101             2/28/2020   $0          1/31/2020
102             2/28/2020   $25         2/28/2020
101             3/31/2020   $20         3/31/2020
102             3/31/2020   $15         3/31/2020
101             4/30/2020   $0          3/31/2020
102             4/30/2020   $0          3/31/2020

Take into consideration that this is a very simplifed data (given confidentiality restrictions) and I'm currently working with a table with 2M plus rows. Note that COUNTROWS also may not work since there are no register for some clients for certain dates.

Answer 1

If I understand correct, you can use this below Measure code or a similar logic to get your required output-

last_purchase_date = 

var current_customer_code = MIN(your_table_name[CustomerCode])
var current_date = MIN(your_table_name[Date])

RETURN
MAXX(
    FILTER(
        ALL(your_table_name),
        your_table_name[CustomerCode] = current_customer_code
        && your_table_name[Date] <= current_date
        && your_table_name[Purchase] > 0
    ),
    your_table_name[Date]
)

Answer 2

Let's give it a try (I'm typing off the top of my head, so apologies for any syntax errors):

Now, since you're talking about a slicer, you really want a measure,not a column, as calculated columns cannot change dynamically, based on a slicer selection.

The easiest solution would be to have a separate date table, that would not be related to your table (and use that table's date column in the slicer). If that were the case, this would be simple.

First, get the selected date: var selectedDate = SELECTEDVALUE(Dates[date])

Then find your date in question: var myDate = CALCULATE(MAX(Fact[date]), FILTER(Fact, Fact[Date] <= selectedDate && Fact[CustomerCode] = '101'))

Now for the complete measure, for instance Purchase to that point:

Purchase Measure before Customer X =
VAR selectedDate =
    SELECTEDVALUE ( Dates[date] )
VAR myDate =
    CALCULATE (
        MAX ( Fact[date] ),
        FILTER ( Fact, Fact[Date] <= selectedDate && Fact[CustomerCode] = '101' )
    )
RETURN
    CALCULATE ( SUM ( Fact[Purchase] ), FILTER ( Fact, Fact[Date] <= myDate ) )

Now, assuming that your slicer is based on the same Date column from the Fact table, that would look a bit different:

Purchase Measure before Customer X =
VAR selectedDate =
    SELECTEDVALUE ( Fact[date] )
VAR myDate =
    CALCULATE (
        MAX ( Fact[date] ),
        FILTER ( ALL(Fact), Fact[Date] <= selectedDate && Fact[CustomerCode] = '101' )
    )
RETURN
    CALCULATE ( SUM ( Fact[Purchase] ), FILTER ( ALL(Fact), Fact[Date] <= myDate ) )

Notice the ALL(Fact) formula in the filter. The filtering context needs to be modified in this example, as the second case is based on the assumption that you're slicing the Fact table and therefore, initially values are only limited to those on the selected date.

Please let me know, if that does it for you. Otherwise, it would be usefule, if you provided more information (like what you would really like to achieve - end result).

EDIT: So, now that I know the purpose, I would approach it differently. I would create a what-if parameter table that would look like this:

When you create a what-if parameter, a measure is also created, in this instance: Value Months since last purchase = SELECTEDVALUE('Months since last purchase'[Months since last purchase], 3)

This measure will tell you (let's assume) that you only want customers who did not make a purchase within the last 3 months.

Let's also assume that you have a measure that shows you the report date (date that you want to calculate the 3 months from). Let's assume it's always TODAY(). Report date = TODAY().

Now I would create a measure that would show me number of days that each customer did not purchase, but only if the number of days is more than X months back.

Days since the last purchase =
VAR last_purchase_date =
    MAX ( Fact[Date] )
RETURN
    IF (
        EDATE ( last_purchase_date, [Value Months since last purchase] ) < [Report date],
        DATEDIFF(last_purchase_date, [Report date], DAY),
        BLANK ()
    )

This should work in a table, where each customer is in a separate row (no need to filter the Max(Fact[Date]) measure in that scenario) or - more general - only one customer is in the measure context.

Since we are returning BLANK(), when last purchase is within the last X months, the table would only show customers who did not make purchase in the last X months (assuming no other measure is in the table).

Here's a working sample: https://1drv.ms/u/s!AmqvMyRqhrBpgtRFbx0p5Uj61ckoaw?e=yOdcXP