cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
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
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!




View solution in original post

11 REPLIES 11
Highlighted
Super User I
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!




View solution in original post

Highlighted

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

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

Changed my world. Thank you. 

Highlighted

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

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

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

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
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

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