cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

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

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 )

1 ACCEPTED SOLUTION
Frequent Visitor

@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 )

6 REPLIES 6
Frequent Visitor

@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 )

Super User

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

Frequent Visitor

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

Super User

@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
Super User

@nattanarcilla
Also please provide copy/pase sample data

Super User

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.