topic Re: How to get revenue for the last date in month in DAX Commands and Tips

How to get revenue for the last date in month

Berl21 — Tue, 23 Aug 2022 17:12:44 GMT

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.

Re: How to get revenue for the last date in month

tamerj1 — Tue, 23 Aug 2022 20:15:02 GMT

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

Re: How to get revenue for the last date in month

Berl21 — Thu, 25 Aug 2022 09:38:20 GMT

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?

Re: How to get revenue for the last date in month

tamerj1 — Thu, 25 Aug 2022 10:04:07 GMT

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