How to show month-end MTD P&L from Yearstart to last month and selected date value for current month (not full month) in a Pivot Table

[prads_uk_2025]

Hi All,

I’m working on a Pivot Table that shows MTD P&L by month for the selected date. Here’s what I’m trying to achieve:

Use Case:

• I have a selected date from a filter:
  👉 20/07/2025
• I want the Pivot to display the following:
• Columns for Jan 25 to Jun 25 → values as of the last available date of each month (month-end) for that Year
• Column for Jul 25 → value as of the selected date (20/07/2025), not for 01/07/2025 to 20/07/2025

• Rows: [Data Type], [Data Category]
• Columns: MonthYear_Reporting  → Date(Floor(MonthEnd("Date")), 'MMM YY')
• Measure (MTD P&L with hedge logic):

[Kaushik2020]

here this uses the max date in the database for the mentioned table. 

Sum({$<Year=, Month=, Day=,
Date={">=$(=date(monthstart(max(Date))))<=$(=date(max(Date)))"}>}
Values

with Monthstart --> we will autopick the first date of the latest month.

then we will take the max date available in the table. 

feel free to revert in case anything. 

[prads_uk_2025 · Accepted solution]

RangeSum(            //YTD to PMTD
Sum({
<
Date = {">=$(=YearStart(Max(Date)))<=$(vPriorMonthEnd)"},
[Month End (Y/N)] = {'Y'},
Year_Reporting = {"$(vMaxYear)"}
>
}
If([Facility Class Type for HYSL Report] <> 'Protection' and [Hedge Type] = 'Hedged' and FundStrategy = 'HY - Europe',
MtdPnlEur * $(vHedgeRatioPerRow),
MtdPnlEur
)
),

Sum({             //SelectedDate
<
Date = {"$(vMaxDate)"}
>
}
If([Facility Class Type for HYSL Report] <> 'Protection' and [Hedge Type] = 'Hedged' and FundStrategy = 'HY - Europe',
MtdPnlEur * $(vHedgeRatioPerRow),
MtdPnlEur
))
)

[prads_uk_2025]

I used RangeSum to calculate YTD to PMTD values first means Jan 2025 to June 2025 and then in other I calculated just Selected Date which will come under July 2025 
MonthYear is in my Pivot Columns. Tricky but at the end, It was worth learning it.