Formula Help - Returning different value for CALCULATE function

[benderbot]

Hey everyone,

I have started a new internship and have to take over the reporting of my predecesor. A collegue wants me to create a new column in a power pivot that returns the dollar value of revenue of the last order for each one of our customers. Generally this power pivot is the list of all orders that have been placed in our system.

The present function is as follows:

=CALCULATE(MAX('shipment - DB'[created_at]); FILTER('shipment - DB'; EARLIER([company_id])= 'shipment - DB'[company_id]))

This function works fine and returns the last order date stamp for each shipment in our database. I.e. now for every row in my power pivot there is a new column that tells me when the last time stamp was when that company order from us.

The only problem is that I want it to return the Revenue dollar amount for that order rather than the date stamp.

Is that possible and if yes how?

Thank you in advance for any help.

 

[Matt Allington]

This doesn't sound like a good approach. Why do you (or your colleague) want a column n your data that has the last order amount? Presumably if a customer has 500 orders, the last order amount will be repeated 500 times, and I guess you don't need it 500 timws. Take a read of my article here Calculated Columns vs Measures in DAX - Excelerator BI

 

[benderbot]

Hey Matt, thanks for your quick response.

I am open to using a measure as long as I can still get a pivot table at the ends with relative columns (company, last order date, order date revenue) in the classic excel interface.

What kind of measure would be able to return a value a coupl columns over of a calculated value? As in the formula as now looks for the max order date (latest) of a specific company, but I want the formula to return the relavant revenue not the time stamp.

 

[akice]

Assuming you only have the company_id on the rows, and no date filter, you should be able to do these measures:

Revenue = SUM(Table[Revenue] )

Last Revenue Date = LASTNONBLANK([COLOR=#333333]'shipment - DB'[created_at]; [Revenue] )[/COLOR]

Last Revenue Amount = CALCULATE( [Revenue];LASTNONBLANK([COLOR=#333333]'shipment - DB'[created_at]; [Revenue] ) [/COLOR])

Although admittedly I prefer to use a dedicated Calendar table and not the fact table date. Also this will Sum up all the sales for the particular company for the most recent date. So if a customer has 2 or more orders, they will be aggregated. This maybe not what you want.