DAX Formula for Cross Table Filtering / Date Logic

[magnify-bi-com]

I am trying to figure out how to write a DAX solution that requires 2 tables being filtering by Date and Status, respectively.

I have 2 tables that are too big to Merge within the Query Editor, and I need a dynamic solution to apply to historic data.

I am looking to find the most recent transaction line per customer in the prevous 90 days in one table, and find the relative status of that transaction from the other table, and only include certain status'.

The tables are linked by a SALEID = OBJID

Pass Table

Sale Table

In the below example, I am looking for a DISTINCTCOUNT of CUSTOMER where the CREATED is in the previous 90 days and the status is 1 OR 2.

I would expect the below test data to return 2, Customer B & C (SALEIDs 1 & 3)

Thanks,

J

[AlexisOlson · Accepted solution]

Yeah. You might want to define it first as a variable though.

Count90 =
VAR DynamicDate = [DynamicDateMeasure]
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Pass'[Customer] ),
        Sale[Created] > DynamicDate - 90,
        'Pass'[Status] IN { 1, 2 }
    )

[AlexisOlson]

How about this?

Count90 = 
CALCULATE (
    DISTINCTCOUNT ( 'Pass'[Customer] ),
    Sale[Created] > TODAY () - 90,
    'Pass'[Status] IN { 1, 2 }
)

[magnify-bi-com]

Thanks for getting back to me Alexis, you're very good, if I wanted this to work for a dynamic date, would I just put my [Date] field where "TODAY()" is?

[AlexisOlson · Accepted solution]

Yeah. You might want to define it first as a variable though.

Count90 =
VAR DynamicDate = [DynamicDateMeasure]
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Pass'[Customer] ),
        Sale[Created] > DynamicDate - 90,
        'Pass'[Status] IN { 1, 2 }
    )

[magnify-bi-com]

Hey Alexis,

I thought this worked but it's actually filtering out the most recent transaction if the status isn't 1 or 2.

What I need to is find the DISTINCTCOUNT of customers, returning their most recent transaction and the related status to that transaction.

I can actually filter out the status within the visual itself, so I don't need that part, but I want to be able to create a dynamic table, that looks back over the last 90 days from any date, and then creates a filtered table that only shows the most recent transaction for that customer and the corresponding status.

Thanks

[AlexisOlson · Accepted solution]

How about this then?

CountLast90 =
VAR DynamicDate = TODAY () - 90
VAR StatusVals = { 1, 2 }
VAR AddCreatedCol =
    ADDCOLUMNS (
        'Pass',
        "Created", LOOKUPVALUE ( Sale[Created], Sale[ObjID], 'Pass'[SaleID] )
    )
VAR PassFiltered = FILTER ( AddCreatedCol, [Created] > DynamicDate )
VAR AddLastCreated =
    ADDCOLUMNS (
        PassFiltered,
        "LastCreated",
            MAXX (
                FILTER ( PassFiltered, [Customer] = EARLIER ( [Customer] ) ),
                [Created]
            )
    )
RETURN
    COUNTROWS (
        SUMMARIZE (
            FILTER ( AddLastCreated, [Created] = [LastCreated] && [Status] IN StatusVals ),
            [Customer]
        )
    )

[magnify-bi-com]

I'm pretty sure you are a genius, that is getting me the overall figure straight off the bat and in a lightening time. Wow, that is impressive! Thank you very much.

If I wanted to put this Measure in a Line chart, with the Date in the Axis, would it be possible that it took the date from the axis and went back 90 days from that date to bring back the count of customers that meet that criteria?

I'm actually blown away by that solution!

[AlexisOlson]

Replace TODAY() with something that reads in the local date context like MAX ( dimDate[Date] ) using whatever your x-axis column is.

[magnify-bi-com]

What would the date measure be?

Essentially, I want to put this Count90 Measure in a table with Dates and want each date to look at the 90 days previous to this date and run this measure. Would it be something like MAX([Date])?

Thanks again for all your help, I really appreciate it.

[AlexisOlson]

Yeah, it would look something like MAX ( Sale[Date] ), which gives the maximum date within the local filter context. So if you had Sale[Date] for the rows in a table visual, it would grab the date from that row.

[AlexisOlson]

Can you give an example that demonstrates what you want the result to be? Your provided samples don't have any matches and only include a single distinct created date and status ID.

[magnify-bi-com]

Hey Alexis,

Apologies, I'm looking for;

A DISTINCTCOUNT of customers who's latest dated transaction line within the previous 90 days has a particular status.

Thanks

[AlexisOlson]

You're much more likely to get an answer if you provide useful examples to work with.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[magnify-bi-com]

In the below example, I am looking for a DISTINCTCOUNT of CUSTOMER where the CREATED is in the previous 90 days and the status is 1 OR 2.

I would expect the below test data to return 2, Customer B & C (SALEIDs 1 & 3)