Skip to main content
cancel
Showing results for 
Search instead 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

Reply
nattanarcilla
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.

 WinWinLossLossWin(%)Win (%)
RegionCurrentYTMCurrentYTMCurrentYTM
1X,XXXX,XXXX,XXX %%
2X,XXXX,XXXX,XXX %%



Data

RegionPersonMonthCategoryValue
1AAprLoss1000
1BAprLoss1500
1CAprLoss1500
1DAprLoss2000
2EMayLoss1000
2FMayLoss2000
2GMayLoss2000
1AMayLoss900
2HJunLoss1000
1CJunLoss1400
1BJunLoss1400
1AJunLoss800


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

 WinWinLossLossWin(%)Win (%)
RegionCurrentYTMCurrentYTMCurrentYTM
1X,XXXX,XXXX,XXX5600%%
2X,XXXX,XXXX,XXX6000%%



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

 

View solution in original post

6 REPLIES 6
nattanarcilla
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 )

 

tamerj1
Super User
Super User

@nattanarcilla 

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

nattanarcilla
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.

tamerj1
Super User
Super User

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

1.png

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

@nattanarcilla 
Also please provide copy/pase sample data

tamerj1
Super User
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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors