DAX Calculate Sum of YTM excluding old value and retaining the latest record

[nattanarcilla]

I'm still new to DAX, this might look simple but I can't figure it out.

I have below Matrix table and I want to get the sum of Loss YTM but exclude old value for a Person since the Loss column in my data is a diminishing value for every person.

	Win	Win	Loss	Loss	Win(%)	Win (%)
Region	Current	YTM	Current	YTM	Current	YTM
1	X,XXX	X,XXX	X,XXX		%	%
2	X,XXX	X,XXX	X,XXX		%	%

Data

Region	Person	Month	Category	Value
1	A	Apr	Loss	1000
1	B	Apr	Loss	1500
1	C	Apr	Loss	1500
1	D	Apr	Loss	2000
2	E	May	Loss	1000
2	F	May	Loss	2000
2	G	May	Loss	2000
1	A	May	Loss	900
2	H	Jun	Loss	1000
1	C	Jun	Loss	1400
1	B	Jun	Loss	1400
1	A	Jun	Loss	800

The result I'm expecting in my matrix for Month of Jun:

	Win	Win	Loss	Loss	Win(%)	Win (%)
Region	Current	YTM	Current	YTM	Current	YTM
1	X,XXX	X,XXX	X,XXX	5600	%	%
2	X,XXX	X,XXX	X,XXX	6000	%	%

Below is the current DAX formula I have to calculate YTM.
I think I need to modify YearToMonthLoss to arrive in the above result to sum only the latest value for each person.

YTM = 
VAR YeartoMonth =
    CALCULATE ( SUM ([Value]),
               [Month] <= max([SlicerMonth]) &&
               [Month] >= min([SlicerMonth])
    )

VAR YearToMonthWin =
    CALCULATE( SUM ([Value]),
               [Month] <= max([SlicerMonth]) &&
               [Month] >= min([SlicerMonth]),
               [Category]="Win"
    )

VAR YearToMonthLoss = 
    CALCULATE( SUM ([Value]),
               [Month] <= max([SlicerMonth]) &&
               [Month] >= min([SlicerMonth]),
               [Category]="Loss"
    )

VAR Result =
    IF( HASONEVALUE ( [Category] ),
        IF( CONTAINS ( Data, [Category], "Loss"),
            YearToMonthLoss,
            FORMAT ( YeartoMonth,"$#,##0" )
        ),
        FORMAT ( DIVIDE (YearToMonthWin, (YearToMonthWin + YearToMonthLoss)),"Percent")
    )

RETURN
    IF( Result = blank(), FORMAT(0,"$#,##0"), Result )

[nattanarcilla · Accepted solution]

@tamerj1thanks for sharing your ideas. I finally was able to get the right solution by adding YTMSummary variable table in my formula.

YTM = 
VAR YeartoMonth =
    CALCULATE ( SUM ([Value]),
               [Month] <= max([SlicerMonth]) &&
               [Month] >= min([SlicerMonth])
    )

VAR YearToMonthWin =
    CALCULATE( SUM ([Value]),
               [Month] <= max([SlicerMonth]) &&
               [Month] >= min([SlicerMonth]),
               [Category]="Win"
    )

VAR YTMSummary =
    SUMMARIZE( Data, [Person],
               "Date", CALCULATE ( MAX( [Month] ),
                                   FILTER ( Data,
                                   [Person] = [Person] &&
                                   [Month] <= max([SlicerMonth]) &&
                                   [Month] >= min([SlicerMonth]) &&
                                   [Category]="Loss" ),
               "MinValue", CALCULATE ( MIN( [Value] ),
                                   FILTER ( Data,
                                   [Person] = [Person] &&
                                   [Month] <= max([SlicerMonth]) &&
                                   [Month] >= min([SlicerMonth]) &&
                                   [Category]="Loss" )
    )

VAR YearToMonthLoss = SUMX ( YTMSummary, [MinValue] )

VAR Result =
    IF( HASONEVALUE ( [Category] ),
        IF( CONTAINS ( Data, [Category], "Loss"),
            FORMAT ( YearToMonthLoss,"$#,##0" ),
            FORMAT ( YeartoMonth,"$#,##0" )
        ),
        FORMAT ( DIVIDE (YearToMonthWin, (YearToMonthWin + YearToMonthLoss) ),"Percent")
    )

RETURN
    IF( Result = blank(), FORMAT(0,"$#,##0"), Result )

[tamerj1]

@nattanarcilla 

Can you share a sample file. I will work on it tomorrow 

[nattanarcilla]

@Tamer sorry I just modified my post, summarizing everything and providing my current formula which was derived from your solution to my previous post.

[tamerj1]

@nattanarcilla 
This is an example of how you can proceed https://www.dropbox.com/t/wffoewX6NztkAVh1

RT Value = 
VAR CurrentDate = MIN ( Data[Date] ) 
VAR CurrentValue = SUM ( Data[Value] )
VAR CurrentRegions = VALUES ( Data[Region] )
VAR TableBefore = CALCULATETABLE ( Data, Data[Date] < CurrentDate, ALLSELECTED ( Data ) )
VAR RemoveOldRegions = FILTER ( TableBefore, NOT ( Data[Region] IN CurrentRegions ) )
VAR PreviousValue = SUMX ( RemoveOldRegions, Data[Value] )
RETURN
    CurrentValue + PreviousValue

[tamerj1]

@nattanarcilla 
Also please provide copy/pase sample data

[tamerj1]

Hi @nattanarcilla 
You need a date, index or at least month number column. I would also recommend that you have a date table (even simple one will do). Can lead you through if you wish.