Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don'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.

Reply
ssk_1984
Helper I
Helper I

Measure to sum values by max of each category by date

Hi Team, Please help to create measure to sum of values by max of each category date, for example here A1 max date is feb, A2 mar'25 and A3 also Mar'25, Here i have sum values of A1 feb+A2 Mar+A3 Mar values by max of date 

 

IDDateLatest DateValue
A101-01-2025 10
A101-02-2025Yes10
A201-01-2025 20
A201-02-2025 20
A201-03-2025yes25
A301-01-2025 30
A301-02-2025 35
A301-03-2025yes40

 

Sum of values for Latest month of all ID =10+25+40 75

2 ACCEPTED SOLUTIONS
DataNinja777
Super User
Super User

Hi @ssk_1984 ,

 

To create a DAX measure that sums the values corresponding to the latest date for each ID in Power BI, you need to follow a structured approach using the SUMX, SUMMARIZE, and CALCULATE functions. The goal is to group the data by ID, find the maximum date for each ID, and then sum the values corresponding to these maximum dates. The following measure achieves this result by first summarizing the table to get the maximum date per ID, and then calculating the sum of values for those maximum dates.

Sum of Latest Values =
SUMX(
    SUMMARIZE(
        'Table',
        'Table'[ID],
        "MaxDate", MAX('Table'[Date])
    ),
    CALCULATE(
        SUM('Table'[Value]),
        'Table'[Date] = [MaxDate]
    )
)

In this measure, the SUMMARIZE function is used to create a summarized table that groups the data by ID and calculates the maximum date for each group. The SUMX function then iterates over this summarized table, and the CALCULATE function filters the original table to match only the rows where the date equals the maximum date for each ID. The resulting values for these rows are summed up to give the desired output.

For your provided data, the calculation will sum the values for the latest month of each ID: 10 for A1 in February, 25 for A2 in March, and 40 for A3 in March. The final result is a total sum of 75.

 

Best regards,

View solution in original post

danextian
Super User
Super User

Hi @ssk_1984 

 

Try either of the following. They will return the same values at the total level but vary at date label.

Latest Values = 
VAR _TBL =
    SUMMARIZE(
        'Table',
        'Table'[ID],
        "MaxDate", CALCULATE(MAX('Table'[Date]))
    )
RETURN
SUMX(
    _TBL,
    CALCULATE(
        SUM('Table'[Value]),
        //acces the max date by id and use it as a filter
        'Table'[Date] = MAXX(_TBL, [MaxDate])
    )
)
Latest Values2 = 
VAR LatestDate =
    CALCULATE ( MAX ( 'Table'[Date] ), ALLEXCEPT ( 'Table', 'Table'[ID] ) )
RETURN
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER ( VALUES ( 'Table'[Date] ), 'Table'[Date] = LatestDate )
    )

danextian_0-1736758330134.png

 










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

View solution in original post

2 REPLIES 2
danextian
Super User
Super User

Hi @ssk_1984 

 

Try either of the following. They will return the same values at the total level but vary at date label.

Latest Values = 
VAR _TBL =
    SUMMARIZE(
        'Table',
        'Table'[ID],
        "MaxDate", CALCULATE(MAX('Table'[Date]))
    )
RETURN
SUMX(
    _TBL,
    CALCULATE(
        SUM('Table'[Value]),
        //acces the max date by id and use it as a filter
        'Table'[Date] = MAXX(_TBL, [MaxDate])
    )
)
Latest Values2 = 
VAR LatestDate =
    CALCULATE ( MAX ( 'Table'[Date] ), ALLEXCEPT ( 'Table', 'Table'[ID] ) )
RETURN
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER ( VALUES ( 'Table'[Date] ), 'Table'[Date] = LatestDate )
    )

danextian_0-1736758330134.png

 










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.
DataNinja777
Super User
Super User

Hi @ssk_1984 ,

 

To create a DAX measure that sums the values corresponding to the latest date for each ID in Power BI, you need to follow a structured approach using the SUMX, SUMMARIZE, and CALCULATE functions. The goal is to group the data by ID, find the maximum date for each ID, and then sum the values corresponding to these maximum dates. The following measure achieves this result by first summarizing the table to get the maximum date per ID, and then calculating the sum of values for those maximum dates.

Sum of Latest Values =
SUMX(
    SUMMARIZE(
        'Table',
        'Table'[ID],
        "MaxDate", MAX('Table'[Date])
    ),
    CALCULATE(
        SUM('Table'[Value]),
        'Table'[Date] = [MaxDate]
    )
)

In this measure, the SUMMARIZE function is used to create a summarized table that groups the data by ID and calculates the maximum date for each group. The SUMX function then iterates over this summarized table, and the CALCULATE function filters the original table to match only the rows where the date equals the maximum date for each ID. The resulting values for these rows are summed up to give the desired output.

For your provided data, the calculation will sum the values for the latest month of each ID: 10 for A1 in February, 25 for A2 in March, and 40 for A3 in March. The final result is a total sum of 75.

 

Best regards,

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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