Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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.
Solved! Go to 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 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.
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]
)
)
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
14 | |
10 | |
10 | |
9 | |
9 |
User | Count |
---|---|
20 | |
13 | |
12 | |
11 | |
8 |