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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors