cancel
Showing results for
Search instead for
Did you mean:
Highlighted
Helper I

## Calculate Sum of Values on Max Date

Trying to calculate revenue for the latest date only. Gave it a shot below but its not working. Any help is appreciated

TodayRev = CALCULATE(SUM(TransSummaryPlus[EstRev3]),Max(TransSummaryPlus[Mdate]))

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User I

Try

TodayRev = CALCULATE(

SUM(TransSummaryPlus[EstRev3]),

FILTER( ALL(TransSummaryPlus), TransSummaryPlus[Mdate] = Max(TransSummaryPlus[Mdate])))

If the latest date will always literally be "today" you could also do

TodayRev = CALCULATE(SUM(TransSummaryPlus[EstRev3]),TransSummaryPlus[Mdate] = TODAY())

That second one will return 0 if there are no sales on the day you're looking at. The first one (and yours if it had worked) would return yesterday's sales if there were no sales today. So the correct solution depends partly on your desired results.

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

Proud to be a Super User!

11 REPLIES 11
Highlighted
Super User I

Try

TodayRev = CALCULATE(

SUM(TransSummaryPlus[EstRev3]),

FILTER( ALL(TransSummaryPlus), TransSummaryPlus[Mdate] = Max(TransSummaryPlus[Mdate])))

If the latest date will always literally be "today" you could also do

TodayRev = CALCULATE(SUM(TransSummaryPlus[EstRev3]),TransSummaryPlus[Mdate] = TODAY())

That second one will return 0 if there are no sales on the day you're looking at. The first one (and yours if it had worked) would return yesterday's sales if there were no sales today. So the correct solution depends partly on your desired results.

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

Proud to be a Super User!

Highlighted
Helper I

Thank you very much! One more quick question ... Can the calculate function pass multiple conditions? In addition to the date being the max date I want to omit a certain payer type ...

Highlighted
Super User I

As many conditions as you like, pretty much. You need a full FILTER statement for it.

TodayRev = CALCULATE(

SUM(TransSummaryPlus[EstRev3]),

FILTER(TransSummaryPlus,

TransSummaryPlus[Mdate] = TODAY() &&

TransSummaryPlus[PayerType] <> "Whatever"))

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

Proud to be a Super User!

Highlighted
Helper I

Changed my world. Thank you.

Highlighted
Super User I

Just wait until you start nesting and/or conditions. I have FILTER statements that are two pages long.

Oh, I meant to give an explanation of why I used ALL() in the first formula. The ALL(TransSummaryPlus) bit is only necessary if you want to make sure that no other filters you add to your report later will ever accidentally move you into the past. It will always give you the last date in the entire dataset, no matter what.

If you removed the ALL() and left it as

TodayRev = CALCULATE(

SUM(TransSummaryPlus[EstRev3]),

FILTER( TransSummaryPlus, TransSummaryPlus[Mdate] = Max(TransSummaryPlus[Mdate])))

...it would appear to behave the same way, unless you started adding extra filters. Say for instance you have a page in your report where you filtered out sales in New Jersey, and today you only had sales in New Jersey. TodayRev would then return yesterday's non-New Jersey sales without warning, because the filter happens first. Without New Jersey the latest date the formula can see is yesterday. On the other hand, my ALL() version would not allow you to filter out New Jersey at all, so neither of these is really the most elegant solution. That's why I suggested forgoing MAX() altogether and gave the [Mdate] = TODAY() version.

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

Proud to be a Super User!

Highlighted
Helper I

Only issue with the TODAY() piece is that my data is no up to date with the current date. So I have to use MaxDate to get the latest date value in the set.

Highlighted
Frequent Visitor

I have slightly different scenerio and I'm stuck. Can anyone help please.

I'm trying to get sum of total storage per server for the latest date available. like below

Server Name         Storage          Date

SeverA

Highlighted
Frequent Visitor

How to find the sum of values for second latest date in the given send of dates?

Highlighted
Frequent Visitor

Can you please help on Calculating Sum of values for second latest date in the set of given dates.

I have to create Sum OF T'days values and Sum of Y'days values, for that i need second latest date.

## Helpful resources

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Microsoft Power Platform Communities

Check out the Winners!

#### Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors