Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Berl21
Helper III
Helper III

How to get revenue for the last date in month

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:

Berl21_0-1661274239573.png

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.

 

 

1 ACCEPTED SOLUTION

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

1.png

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

View solution in original post

3 REPLIES 3
Berl21
Helper III
Helper III

@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?

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

1.png

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
Super User
Super User

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

1.png

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!