Calculate Sum of Values on Max Date

[hansolo]

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]))

[KHorseman · Accepted solution]

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.

[Anonymous]

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

[hansolo]

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

[KHorseman]

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"))

[Mounika1]

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.

[KHorseman]

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.

[Ash1]

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]

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]

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

[hansolo]

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. 

[hansolo]

Changed my world. Thank you.