Forum Discussion
DAX Formula for Cross Table Filtering / Date Logic
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 } )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] ) )
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.
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
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
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)