Help to flag the maximum date within an "in force contract" date range selected by user

[DavidWaters]

Hello,

I have been stuck on a solution to this for 2 hours and am running out of time!  I wonder if anyone could help please?

I have the below data and an "In force" filter which users can choose a date for

Reference	Transaction Date	Max_trans_Date_Flag
A1	01/10/2024	1
A1	24/12/2024	1

The result of "in force" measure is set to greater than zero only by filter to isolate just those "in force".  The date selected by user is 31/12/24, so the above two show OK, 2025 dates drop off which is what i want

InForceContracts =

CALCULATE(

COUNTROWS('Table'),

FILTER(

'Table',

'Table'[TRANSACTION_EFFECTIVE_DATE] <= SELECTEDVALUE('In force period'[Date]) && 'Table'[TRANSACTION_EXPIRATION_DATE] >= SELECTEDVALUE('In force period'[Date])

)

)

However, I want to then flag only the latest date for each reference remaining with a 1, but I cam getting a 1 next to both dates and I can't get it to work!

Can anyone help please?  Below is what I tried and it's not working (one of many attempts!)

MaxTransDateFlag =
IF(
ISBLANK(
CALCULATE(
MAX('Table'[TRANSACTION_EFFECTIVE_DATE]),ALLEXCEPT('Table','Table'[Reference])
)
),
0,
1
)

[DavidWaters]

 @Ashish_Excel hello please see link below to the data and problem:

https://drive.google.com/file/d/1F13wJ5z47Y_M4XNImolsnPgXVOMCoTfC/view?usp=drive_link

[v-dineshya · Accepted solution]

Hi @DavidWaters ,

Thank you for reaching out to the Microsoft Community Forum.

Please refer  below DAX measure for MaxTransDate.

MaxTransDateFlag1 =

VAR SelectedDate = SELECTEDVALUE('calendar'[Date])

VAR CurrentRef = SELECTEDVALUE('Table'[Ref 1])

VAR CurrentDate = SELECTEDVALUE('Table'[TRANSACTION_EFFECTIVE_DATE])

VAR LatestTransDate =

    CALCULATE(

        MAX('Table'[TRANSACTION_EFFECTIVE_DATE]),

        FILTER(

            ALL('Table'),

            'Table'[Ref 1] = CurrentRef &&

            'Table'[TRANSACTION_EFFECTIVE_DATE] <= SelectedDate &&

            'Table'[TRANSACTION_EXPIRY_DATE] >= SelectedDate

        )

    )

RETURN

IF (

    CurrentDate = LatestTransDate,

    1,

    0

)

If my response has resolved your query, please mark it as the "Accepted Solution" to assist others. Additionally, a "Kudos" would be appreciated if you found my response helpful.

Thank you

[DavidWaters]

@v-dineshya Ah I see how you have done this - makes sense, thanks so much for you help. It is working well!

[DavidWaters]

Hi @Ashish_Excel 

Sorry below is the result I am trying to achieve.  The user selects an "inforce" date of 31/12/24 which results in a 1 or 0 in "InForceContracts" so 2025 is exlcuded by not returning a 1 - this is working OK per the measure in my original post.  However it is the Max TransactionPerRef which needs to return a 1 against the latest date within that reference and 0 for the others that are older.  The measure I tried in my original post is returning 1 against all dates.  That's the measure that is not working.  It has to be a measure because it needs to respond dynamically to the "inforcecontracts" period selected too. Thanks

Ref 1	TRANSACTION_EFFECTIVE_DATE	InForceContracts	Max transaction per ref
A	01/10/2024 00:00	1	0
A	24/12/2024 00:00	1	1
A	15/01/2025 00:00
B	01/10/2024 00:00	1	0
B	24/12/2024 00:00	1	0
B	25/12/2024 00:00		1

[Ashish_Excel]

It will be easier for me to help you if you share the download link of the PBI file.  Share only the required tables/columns.

[Ashish_Excel]

Hi,

Your question is not clear.  Share some data to work with and show the expected result.  Share data in a format that can be pasted in an MS Excel file.