Difficult sum

[IrisW]

In my report, I have a date filter. With this I select one date, for example Dec. 31, 2023.

My source data is structured as follows:

ID	Startdate	Untildate	Value
1	Nov. 1, 2023	Nov. 2, 2023	40
1	Nov. 2, 2023	Nov. 3, 2023	50
1	Nov. 3, 2023	Nov. 4, 2023	60
1	Feb. 1, 2024	Feb. 2, 2024	80
2	Nov. 2, 2023	Nov. 3, 2023	50
2	Nov. 3, 2023	Nov. 4, 2023	40
3	Nov. 13, 2023	Nov. 14, 2023	50
3	Nov. 14, 2023	Nov. 15, 2023	60

Power BI should give me the sum of all the Values where the date in the Untildate column is largest (per ID) under the selected date. So in this example, 60+40+60 = 160.

ID	Startdate	Untildate	Value
1	Nov. 1, 2023	Nov. 2, 2023	40
1	Nov. 2, 2023	Nov. 3, 2023	50
1	Nov. 3, 2023	Nov. 4, 2023	60
1	Feb. 1, 2024	Feb. 2, 2024	80
2	Nov. 2, 2023	Nov. 3, 2023	50
2	Nov. 3, 2023	Nov. 4, 2023	40
3	Nov. 13, 2023	Nov. 14, 2023	50
3	Nov. 14, 2023	Nov. 15, 2023	60

Is this possible?

[ThxAlot · Accepted solution]

[bhanu_gautam]

@IrisW ,  Create a measure to find the largest Untildate per ID that is under the selected date.
Create another measure to sum the values corresponding to these largest Untildate values.

DAX
Max_Untildate_Under_Selected =
CALCULATE(
MAX('Table'[Untildate]),
FILTER(
'Table',
'Table'[Untildate] <= SELECTEDVALUE('Date'[Date])
)
)

Sum_Values_Largest_Untildate =
SUMX(
SUMMARIZE(
'Table',
'Table'[ID],
"Max_Untildate", [Max_Untildate_Under_Selected]
),
CALCULATE(
SUM('Table'[Value]),
'Table'[Untildate] = [Max_Untildate]
)
)