Forum Discussion
magnify-bi-com
4 years agoHelper I
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...
- 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 } ) - 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] ) )
magnify-bi-com
4 years agoHelper I
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
4 years agoSuper User
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.