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
Natron
New Member

Sum of Max Values over time

Hello! 

 

I am trying to calculate a salary increase curve over time. Employees get annual increases or raises annually or semi-annually. I want to show the increasing value by department group. 

 

Here's a sample of the data: 

EmpDeptEffDatePrior EffDateAMT
1A6/22/20096/22/2009430
1A6/22/20106/22/2009480
1A6/22/20116/22/2010491
1A6/22/20126/22/2011499
1A6/22/20136/22/2012516
1A3/31/20146/22/2013520
1A6/23/20143/31/2014538
1A12/10/20146/23/2014578
1A6/22/201512/10/2014597
1A7/27/20156/22/2015597
1A8/4/20157/27/2015674
1A6/22/20168/4/2015692
1A6/22/20176/22/2016714
1A1/29/20186/22/2017670
1A4/1/20181/29/2018710
1A6/22/20184/1/2018733
1A6/22/20196/22/2018756
1A6/22/20206/22/2019770
1A6/22/20216/22/2020785
1A2/21/20226/22/2021825
1A6/22/20222/21/2022889
1A11/13/20226/22/2022917
1A6/22/202311/13/2022943
1A4/1/20246/22/2023983
2A8/1/20118/1/20111412
2A1/1/20128/1/20111412
2A1/1/20131/1/20121441
2A1/1/20141/1/20131471
2A9/8/20141/1/20141547
2A1/1/20159/8/20141595
2A1/1/20161/1/20151648
2A1/1/20171/1/20161707
2A1/1/20181/1/20171769
2A1/1/20191/1/20181833
2A1/1/20201/1/20191903
2A1/1/20211/1/20201976
2A1/1/20221/1/20212078
2A1/1/20231/1/20222184
2A1/1/20241/1/20232363
3B8/6/20188/6/2018758
3B8/6/20198/6/2018784
3B2/24/20208/6/2019809
3B8/6/20202/24/2020895
3B8/6/20218/6/2020945
3B1/1/20228/6/20211142
3B1/1/20231/1/20221256
3B7/1/20231/1/20231313
3B1/1/20247/1/20231431
4B3/30/20203/30/20202065
4B1/1/20213/30/20202176
4B1/1/20221/1/20212321
4B1/1/20231/1/20222600
4B1/1/20241/1/20232765
5A5/3/20215/3/20213123
5A1/1/20225/3/20213220
5A1/1/20231/1/20223367
5A1/1/20241/1/20233674

 

I want to sum the max values in a way that respects the effective dates against a generic DATE table, which I've just caluclated with DAX- 

Dates = CALENDAR(Date(1997,1,1), today())
 
There is no relationship between the data above and the date table - in the measure below, I am using the Dates[Date] column as an axis in a graph, and sum the amounts against these dates.
 

Here's what I've tried: 

Date.Amt =
VAR __DATE = MAX ( 'Dates'[Date] )
 
RETURN

    SUMX (
        TABLE,
        IF (
             TABLE[EffDate] <= __DATE && __DATE >  TABLE[Prior EffDate],
            Calculate(max( TABLE[AMT])),
            BLANK ()
        )
    )
 
Against a generic date, the correct total across all for today would be $8450, and for dept B it should be $4196. But at year end 2023 it should be $3913. 
 
I want something that looks like this in the end - but this measure is summing all the prior values and so is overstated. I've tried a few variations of the measure above but I can't make it work.  
 
Natron_0-1717590750193.png

 

 
1 ACCEPTED SOLUTION
xifeng_L
Solution Sage
Solution Sage

Hi @Natron 

 

You can try this measure.

 

xifeng_L_0-1717598428165.png

Measure = 
SUMX(
    SUMMARIZE('Table','Table'[Dept],'Table'[Emp]),
    CALCULATE(
        CALCULATE(
            MAX('Table'[AMT]),
            LASTDATE('Table'[EffDate])
        ),
        'Table'[EffDate]<=MAX('Calendar'[Date])
    )
)

 

The results I calculated for dept B can be matched with the correct results you provided, but the total of 8450 I can't come up with and it's not clear if you wrote it down incorrectly?

 

 

Demo - Sum of Max Values over time.pbix

 

 

 

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

 

Thank you~

View solution in original post

3 REPLIES 3
xifeng_L
Solution Sage
Solution Sage

Hi @Natron 

 

You can try this measure.

 

xifeng_L_0-1717598428165.png

Measure = 
SUMX(
    SUMMARIZE('Table','Table'[Dept],'Table'[Emp]),
    CALCULATE(
        CALCULATE(
            MAX('Table'[AMT]),
            LASTDATE('Table'[EffDate])
        ),
        'Table'[EffDate]<=MAX('Calendar'[Date])
    )
)

 

The results I calculated for dept B can be matched with the correct results you provided, but the total of 8450 I can't come up with and it's not clear if you wrote it down incorrectly?

 

 

Demo - Sum of Max Values over time.pbix

 

 

 

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

 

Thank you~

This worked! Thank you! 

BeaBF
Solution Sage
Solution Sage

@Natron Hi! Try with this steps:

// Calculated column for the latest effective date


LatestEffDate =
CALCULATE (
MAX ( Table[EffDate] ),
ALLEXCEPT ( Table, Table[Emp], Table[Dept] ),
Table[EffDate] <= EARLIER ( Table[EffDate] )
)

// Measure to sum the latest effective amounts


Date.Amt =
VAR __DATE = MAX ( 'Dates'[Date] )

RETURN
SUMX (
FILTER (
Table,
Table[EffDate] <= __DATE &&
Table[EffDate] = Table[LatestEffDate]
),
Table[AMT]
)

 

BBF

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.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.