Forum Discussion
Helper IISelect max value from the date slicer
Hi Ashish
Below is the data.Activity
MonthYear
Value
Monthly Clients Churn
Monday, 1 January 2024
0.052
Monthly Clients Churn
Thursday, 1 February 2024
0.022
Monthly Clients Churn
Friday, 1 March 2024
0.0515
Monthly Clients Churn
Monday, 1 April 2024
0.02255
Monthly Clients Churn
Wednesday, 1 May 2024
0.022
Monthly Clients Churn
Saturday, 1 June 2024
0.051
Monthly Clients Churn
Monday, 1 July 2024
0.04
Monthly Clients Churn
Thursday, 1 August 2024
0.051
Monthly Clients Churn
Sunday, 1 September 2024
0.015
Monthly Clients Churn
Tuesday, 1 October 2024
0.051
Monthly Clients Churn
Friday, 1 November 2024
0
Monthly Clients Churn
Sunday, 1 December 2024
0
Monthly Clients Churn
Wednesday, 1 January 2025
0
Monthly Clients Churn
Saturday, 1 February 2025
0
Monthly Clients Churn
Saturday, 1 March 2025
0
Monthly Clients Churn
Tuesday, 1 April 2025
0
Monthly Clients Churn
Thursday, 1 May 2025
0
Monthly Clients Churn
Sunday, 1 June 2025
0
When Selected in Slicer as Q1 or just July, the data should be displayed for July, if selected for Q1, should display data for Sept2024
Q1
Jul-24
Aug-24
Sep-24
Q2
Oct-24
Nov-24
Dec-24
Q3
Mar-24
Q4
Jun-25
Whole of 2024 as Year
Jun-25
Super UserUpdated Measure for Monthly Churn with Quarterly Selection
Monthly Churn =
VAR __StartDate = MIN('DateTable'[Date])
VAR __EndDate = MAX('DateTable'[Date])
-- Find the last month in the selected quarter or year
VAR __MaxDateInPeriod =
CALCULATE(
MAX('DateTable'[Date]),
'DateTable'[Date] <= __EndDate,
'DateTable'[IsEndOfMonth] = TRUE
)
-- Retrieve the churn value for the last month in the selected period
RETURN
CALCULATE(
MAXX(
FILTER(
'PS NPS CSAT Lapses Data',
'PS NPS CSAT Lapses Data'[Activity] = "Monthly Clients Churn" &&
'PS NPS CSAT Lapses Data'[Month_Year] = __MaxDateInPeriod
),
'PS NPS CSAT Lapses Data'[Value]
)
)
Now that we have __MaxDateInPeriod, we use it in a FILTER to ensure the measure retrieves the correct value for the end of the quarter, month, or year, depending on the selection.
💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn
reddevilHi Kedar,
Thanks for replying, but the above measure shows blank for Q1 as well, it is still not working. I tried with my measure and it is still working till Q1 but when i select Q2 it is filtering out as I think the December value is blank.