topic Re: DAX Calculate Sum of YTM excluding old value and retaining the latest record in DAX Commands and Tips

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

nattanarcilla — Thu, 23 Jun 2022 14:13:37 GMT

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.

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

tamerj1 — Thu, 23 Jun 2022 12:45:52 GMT

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.

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

tamerj1 — Thu, 23 Jun 2022 12:51:07 GMT

nattanarcilla
Also please provide copy/pase sample data

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

tamerj1 — Thu, 23 Jun 2022 13:22:22 GMT

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

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

nattanarcilla — Thu, 23 Jun 2022 14:25:45 GMT

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

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

tamerj1 — Thu, 23 Jun 2022 16:53:52 GMT

nattanarcilla

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

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

nattanarcilla — Fri, 24 Jun 2022 03:56:28 GMT

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 )