topic Re: Trying to calculate SUM based on max date in DAX Commands and Tips

Trying to calculate SUM based on max date

Anonymous — Tue, 01 Feb 2022 15:05:56 GMT

I have a snapshotted fact table and I wish to return the sum of a column based on the max date in a slicer. So for example:

ID	TotalCapacity	Date
1	25	2022/01/01
2	40	2022/01/01
1	65	2022/02/01
2	40	2022/02/01
1	80	2022/03/01
2	50	2022/03/01

If the max date selected on the filter is 2022/01/01 I want to return 65 as my sum, if it is 2022/02/01 I want to return 105, if it is 2022/03/01 I want to return 130. How can I do this? Thanks for the help!

Re: Trying to calculate SUM based on max date

ValtteriN — Tue, 01 Feb 2022 15:10:01 GMT

Hi,

Try something like this:
Measure =
var _maxdate = MAX(Table[Date]) Return
CALCULATE(SUM(Table[Value]),ALL(Table),Table[Date]=_Maxdate)

You can alternatively use e.g. Calendar[Date] for _maxdate reference.

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/