Forum Discussion

magnify-bi-com's avatar
magnify-bi-com
Helper I
4 years ago
Solved

DAX Formula for Cross Table Filtering / Date Logic

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, a...
Need Help
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    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's avatar
    AlexisOlson
    4 years ago

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

     

AlexisOlson's avatar
AlexisOlson
Super User
4 years ago

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's avatar
magnify-bi-com
Helper I
4 years ago

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's avatar
    AlexisOlson
    Super User
    4 years ago

    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.