Different Dates based upon 2 different columns

[antlufc]

The table below shows MQLs by latest date and latest event flag. 

I would like to show the volume of MQLs that are Not Accepted or Achived using the MQLs - latest date. 

So in my below examples i would like Key 7442051 to be counted and use the date 22/9/2023 and 7085123 to be counted and use 1/9/2023. It may also occur where they are on the same date and some instances where Not Accpted and Archived are either not the latest event or may not appear at all. 

I can not add a column or amend the power query so i would like to solve this problem using DAX. 

(Column Names as per below, this is only in relation to the table below

Column 1 = Stage Name
Column 2 = MQLs latest Date

Column 3 = latest event flag
Colimn 4 = date

Column 5 = Key)

New	1	0	22/09/2023	7442051
Working	0	0	22/09/2023	7442051
Working	0	0	26/09/2023	7442051
Working	0	0	27/09/2023	7442051
Not Accepted	0	1	27/09/2023	7442051
New	0	0	09/08/2023	7085123
New	1	0	01/09/2023	7085123
Working	0	0	01/09/2023	7085123
Working	0	0	05/09/2023	7085123
Archived	0	1	05/09/2023	7085123

[antlufc]

@Greg_Deckler sorry forgot to mention stage name is in a different table to latest evcent flag however they linked via a relationship 

[Greg_Deckler]

@antlufc What is the relationship? As in, what forms the relationship?

[antlufc]

@Greg_Deckler  - date key based on event date from the fact table to date key in the date dimension.  The event date could be associated with events prior and post the MQL latest date

[Greg_Deckler]

@antlufc I'm not quite following this. So you have a Date dimension and that includes what the Stage is for each Key based on which Date? Maybe post a picture of your data model or at least the relevant tables. I'm not understanding how you are arriving at the correct Stage for a specified Key and Date.

[antlufc]

@Greg_Deckler 

date dimension - which provides the date linked to fact events via date key

fact events table -  which provides the MQL latest date, key and date key which links to date dimension based on the date an event happened and dim events via the key

dim events -  which contains the stage name which is linked to fact events via the key. 

[Greg_Deckler]

@antlufc So the dim events table also has a date so you know what stage a key was in on a specific date?

[antlufc]

@Greg_Deckler - no dim events only has the stage name. 

The date where the specific event has happened is in fact events. 

[antlufc]

@Greg_Deckler - i need to take into account if the MQL is Archived / Not Accpeted and take into account the date of the latest MQL. 

Would be - provide the count of MQLs latest date where the latest event flag = 1 and the stage name = Not Accpted or Archived. 

[Greg_Deckler]

@antlufc OK, how about this? PBIX attached below signature.

Measure = 
  VAR __Statuses = { "Archived", "Not Accepted" }
  VAR __LatestStatus = MAXX( FILTER( 'Table', [latest event flag] = 1 ), [Stage Name] )
  VAR __MaxDate = MAXX( FILTER('Table', [MQLs latest Date] = 1), [date] )
  VAR __Result = IF( __LatestStatus IN __Statuses, __MaxDate, BLANK() )
RETURN
  __Result

[Greg_Deckler]

@antlufc Not sure I fully understand but perhaps something like:

Measure =
  VAR __Table = FILTER('Table', [MQLs latest Date] = 1)
  VAR __MaxDate = MAXX( __Table, [date] )
RETURN
  __MaxDate