Sum most recent value with group conditions

[Tony_Tony]

Hi there!
I think I'm close to getting my desired output but somehow I am missing part of the DAX and it doesnt add up in the Total. I have this data and the goal is to identify the most recent one per ticket age, display in the table (that part I was able to get). However, the DAX that I've tried does not add up in the total (kindly see image below) so I can display the how many tickets are open.

ACCOUNT NAME	DATE	DAY	MONTH	TICKET AGE	Total Tickets
A	2/27/2023	Mon	Feb-23	<1 Day	0
A	2/27/2023	Mon	Feb-23	1-3 Days	15
A	2/27/2023	Mon	Feb-23	4-10 Days	86
A	2/27/2023	Mon	Feb-23	11-20 Days	16
A	2/27/2023	Mon	Feb-23	>20 Days	2
B	2/27/2023	Mon	Feb-23	<1 Day	72
B	2/27/2023	Mon	Feb-23	1-3 Days	293
B	2/27/2023	Mon	Feb-23	4-10 Days	145
B	2/27/2023	Mon	Feb-23	11-20 Days	24
B	2/27/2023	Mon	Feb-23	>20 Days	13
C	2/27/2023	Mon	Feb-23	<1 Day	8
C	2/27/2023	Mon	Feb-23	1-3 Days	103
C	2/27/2023	Mon	Feb-23	4-10 Days	44
C	2/27/2023	Mon	Feb-23	11-20 Days	3
C	2/27/2023	Mon	Feb-23	>20 Days	0
A	2/28/2023	Tue	Feb-23	<1 Day	0
A	2/28/2023	Tue	Feb-23	1-3 Days	15
A	2/28/2023	Tue	Feb-23	4-10 Days	86
A	2/28/2023	Tue	Feb-23	11-20 Days	16
A	2/28/2023	Tue	Feb-23	>20 Days	2
C	2/28/2023	Tue	Feb-23	<1 Day	8
C	2/28/2023	Tue	Feb-23	1-3 Days	103
C	2/28/2023	Tue	Feb-23	4-10 Days	44
C	2/28/2023	Tue	Feb-23	11-20 Days	3
C	2/28/2023	Tue	Feb-23	>20 Days	0
B	2/28/2023	Tue	Feb-23	<1 Day	72
B	2/28/2023	Tue	Feb-23	1-3 Days	293
B	2/28/2023	Tue	Feb-23	4-10 Days	145
B	2/28/2023	Tue	Feb-23	11-20 Days	24
B	2/28/2023	Tue	Feb-23	>20 Days	13

Table visual in Power BI

I tried a number of dax measures and the last one shown in the table with the max values is below. Kindly let me know how to proceed. Thank you in advance.

Ticket <1 Day =

VAR MAX_D = MAX(Source[DATE])

VAR OneDay= CALCULATE(

                            SUM(Source[TICKET AGE COUNT]),

                            Source[TICKET AGE]="<1 Day",

                            Source[DATE]=MAX_D

                )

RETURN

OneDay

[v-jianboli-msft · Accepted solution]

Hi @Tony_Tony ,

Please try:

Measure = 
SUMX(FILTER('Source',[TICKET AGE]="<1 Day"&&[DATE]=CALCULATE(MAX('Source'[DATE]),FILTER('Source',[ACCOUNT NAME]=EARLIER(Source[ACCOUNT NAME])))),[Total Tickets])

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Mahesh0016]

@Tony_Tony i hope this helps you!THANK YOU!!

Ticket <1 Day =

VAR MAX_D = MAX('Table E'[DATE])

VAR OneDay= CALCULATE(

                            SUM('Table E'[Total Tickets]),

                            FILTER(ALLSELECTED('Table E'[DATE]),'Table E'[DATE] = MAX_D),

                            ALLEXCEPT('Table E','Table E'[TICKET AGE])

                )

RETURN

OneDay

[Tony_Tony]

Thank you @Mahesh0016 for the reply. However, when I apply the function, I think it's adding up the latest entry of the Accounts column with the same max date. I tried other measure but it doesnt work when I use a month slicer. For June, it shows the max data just right but when I selected May, its blank. This is the output when I utilised the above measure