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.
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
ID | Date | Latest Date | Value |
A1 | 01-01-2025 | 10 | |
A1 | 01-02-2025 | Yes | 10 |
A2 | 01-01-2025 | 20 | |
A2 | 01-02-2025 | 20 | |
A2 | 01-03-2025 | yes | 25 |
A3 | 01-01-2025 | 30 | |
A3 | 01-02-2025 | 35 | |
A3 | 01-03-2025 | yes | 40 |
Sum of values for Latest month of all ID =10+25+40 75
Solved! Go to Solution.
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,
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 )
)
Proud to be a 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 )
)
Proud to be a 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,
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
142 | |
76 | |
63 | |
51 | |
47 |
User | Count |
---|---|
211 | |
82 | |
61 | |
61 | |
60 |