Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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 )
Solved! Go to 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 )
@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 )
@Tamer sorry I just modified my post, summarizing everything and providing my current formula which was derived from your solution to my previous post.
@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
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
21 | |
15 | |
15 | |
11 | |
7 |
User | Count |
---|---|
25 | |
24 | |
12 | |
12 | |
12 |