How to get revenue for the last date in month

[Berl21]

Hi,

I need to write a measure for revenue based on a changing max date. 

This is the problem: 
in my raw data, I get the date for each stage change, meaning that one order will have for example 3 milestones with a different date and revenue associated. I need to report the revenue for the latest change within a milestones and a month and ignore all other revenue information.

Let's say I have only one order to consider. Left is what I get as raw data from my databank, right is what I need to report:

Is there anyway to define the revenue per milestone and link the max date information with a slicer (=showing up the last day of each month for the report to automatically adapt)?

Any help for this will be really appreciated!

Thanks,

Pauline.

[Berl21]

@tamerj1 a matrix won't help as I have cases where there are two entries with a date within the same month and milestones. In this case, I need to select the last date of the two.
I was thinking that the function Rank- Partition would help here, but I have an issue with a format in Power Query, so I can't use it in this form.
Is there anyway to use rank partition as part of a measure?

[tamerj1 · Accepted solution]

Hi @Berl21 
Attached is a modified version of the file.

Revenue Measure = 
SUMX ( 
    Data,
    VAR CurrentMilestoneTable =
        CALCULATETABLE ( Data, ALLEXCEPT ( Data, Data[Milestone] ) )
    VAR LastMilestoneDate = 
        MAXX ( CurrentMilestoneTable, Data[Change Date] )
    RETURN 
        IF (
            Data[Change Date] = LastMilestoneDate,
            Data[Revenue]
        )
)

[tamerj1]

Hi @Berl21 
Why don't you just place it in a matrix visual? See attached file