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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.