MAX date of selected period keeping the year context

[GriRim]

Hi all,

I gave up with Chat GPT so I'll try my luck here.

Desired result: user selects period and measure brings latest date for that period's year selected.

This measure will be used to select proper exchange rate. The rule here  -  system should select latest selected period rate, keeping year in a context.

Scenarious	Selected Period	2023 Rate	2024 Rate	2025 Rate
1	Nov 2024, Dec 2024, Jan 2025, Feb 2025, Mar 2025		31/12/2024	31/03/2025
2	Mar 2023, Apr 2024, May 2025	31/03/2023	30/04/2024	31/03/2025
3	Feb 2024, May 2024		31/05/2024

Dim Date table consists of five columns:

FINANCIAL_Date Date column

 FINANCIAL_MONTH Month number

 FINANCIAL_QTR Quarter number

 FINANCIAL_YEAR Year number

FINANCIAL_YEAR_MONTH Combination of Year and month

Periods will be selected on combination:  FINANCIAL_YEAR & FINANCIAL_MONTH

Can someone help me with the DAX?

This DAX brings the latested selected period (not keeping year in a contex):

 CALCULATE( MAX('Dim Date'[FINANCIAL_DATE]), ALLSELECTED('Dim Date'[FINANCIAL_YEAR_MONTH]))

[bhanu_gautam · Accepted solution]

@GriRim , Try using

dax
LatestSelectedPeriodDate =
VAR SelectedYear = MAX('Dim Date'[FINANCIAL_YEAR])
RETURN
CALCULATE(
MAX('Dim Date'[FINANCIAL_DATE]),
FILTER(
ALLSELECTED('Dim Date'),
'Dim Date'[FINANCIAL_YEAR] = SelectedYear
)
)

[DataNinja777 · Accepted solution]

Hi @GriRim ,

To get the latest selected period while keeping the financial year context, you need to avoid using ALLSELECTED('Dim Date'[FINANCIAL_YEAR_MONTH]) alone because it removes the year context, resulting in a single max date across all selected periods. Instead, use a measure that filters the selected periods but retains the year context. The following DAX formula ensures that the latest date is picked within the same financial year as the current context:

MaxDate_PerYear = 
CALCULATE(
    MAX('Dim Date'[FINANCIAL_DATE]),
    FILTER(
        ALLSELECTED('Dim Date'),
        'Dim Date'[FINANCIAL_YEAR] = MAX('Dim Date'[FINANCIAL_YEAR])
    )
)

This measure works correctly in visuals that have FINANCIAL_YEAR in the row context. It evaluates the maximum date from the selected periods, but only for rows where the year matches the current year in context, effectively giving you the latest period date for each year based on what's selected.

Best regards,

[bhanu_gautam · Accepted solution]

@GriRim , Try using

dax
LatestSelectedPeriodDate =
VAR SelectedYear = MAX('Dim Date'[FINANCIAL_YEAR])
RETURN
CALCULATE(
MAX('Dim Date'[FINANCIAL_DATE]),
FILTER(
ALLSELECTED('Dim Date'),
'Dim Date'[FINANCIAL_YEAR] = SelectedYear
)
)