cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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

Proud to be a Super User!

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

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

Community Champion

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

Proud to be a Super User!

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.

Community Champion

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.

Proud to be a Super User!

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

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,

Frequent Visitor

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

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.

Helper I

Changed my world. Thank you.

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors