Select max value from the date slicer

[reddevil]

Hello everyone,

I have a date slicer which shows the Year, Quarters and Months as shown below:

Scenario:

When I select Quarters or Months, I want the last value of the corresponding quarter and last month of that quarter, eg: If I select Q1 the month value for Sept 2024 should be displayed, for Q4 the month value for Dec 2024 should be displayed and for Year 2024 the last month of the Q4(i.e. June 2024) value must be displayed on a card.

Monthly Churn =
VAR __StartDate = MIN('DateTable'[Date])
VAR __EndDate = MAX('DateTable'[Date])
VAR __MaxDate = MAXX(FILTER(' PS NPS CSAT Lapses Data',[Month_Year] >= __StartDate && [Month_Year]<=__EndDate),[Month_Year])
RETURN
MAXX(FILTER(' PS NPS CSAT Lapses Data',' PS NPS CSAT Lapses Data'[Activity] = "Monthly Clients Churn" &&' PS NPS CSAT Lapses Data'[Month_Year] = __MaxDate),' PS NPS CSAT Lapses Data'[Value])

I am using the above measure, but when i select Q2 the data is displayed as 0.00%: 

The above measure does display months data correctly and also Q1 data correctly, but when i select Q2 it is showing up as Blanks.

Please suggest any improvements required to the above logic.

Thanks 

[reddevil]

Thanks everyone for their replies, I used my original measure and adjusted relationship with my date table to come up with a solution.

[Kedar_Pande]

@reddevil 

Updated 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

[reddevil]

Hi 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.

[Ashish_Mathur]

Hi,

Does this measure work?

=calculate(max(' PS NPS CSAT Lapses Data'[Value]),datesbetween('Datetable'[Date],max('Datetable'[Date]),max('Datetable'[Date])),' PS NPS CSAT Lapses Data'[Activity] = "Monthly Clients Churn")

[reddevil]

Hi Ashish

Thanks for replying, the above measure does not work as it doesnt filter the months and the value.

[Ashish_Mathur]

You are welcome.  Why did you mark my reply as Answer then?  Share some data to work with, explain the question and show the expected result.  Share data in a format that can be pasted in an MS Excel file.

[reddevil · Accepted solution]

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

[Ashish_Mathur]

Not sure what you want but here is my attempt

[reddevil]

Thanks Ashish, what if you select Q2 or Full Year.

[Ashish_Mathur]

What answer are you expecting when you select Q2 of FY 2024-25?  What answer are you expecting when you select FY 2024-25.  Share the answers after reviewing my file.

[Ashish_Mathur]

Try that yourself.  If the result does not match your expectation, then clearly post back the result you are expecting.

[reddevil]

What if you select Q1 and Q2 together, its showing up as blank.

[reddevil]

apologies, i just thought it was reply 😞