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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
hansolo
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
KHorseman
Community Champion
Community Champion

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
KHorseman
Community Champion
Community Champion

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!




Anonymous
Not applicable

That didnt work at all!

CALCULATE(
SUM(Opportunity[Time to first Contact]),
FILTER(ALL(Opportunity),
Opportunity[CreatedDate] = MAX(Opportunity[CreatedDate])
)
)
I wrote this and I get BLANK. Please advise

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

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!




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.

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!




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

Archie1
Frequent Visitor

Hi @Ash1,

 

I am not sure if you had got solution for your query below.

 

I am came across the same issue and found solution for it and it is:

You need to create 2 measures.

Measure 1: Last or Latest date should be created as measure (which you already had)

(For New users here is the formula :  

LastORLatestOrdercreatedbasedonOrderdate_M = CALCULATE( LASTDATE('TableName'[Date]),ALLEXCEPT('TableName','TableName'[Filter1],'TableName'[Filter2])))

 

Measure 2: Now create another measure to get total sum .

Here is total sum formula pointing to above date measure:

Last/LatestOrderCreated$bylatestorderdate = CALCULATE(SUM(Tablename[SalePrice]), FILTER(Tablename, TableName[Date] = [LastORLatestOrdercreatedbasedonOrderdate_M ]) )

 

Should be able to achieve your expected output.

 

Hope solution is not too late 😛

 

Cheers, 

Mounika1
Frequent Visitor

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

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. 

Changed my world. Thank you. 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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