Calculate difference between Amount based on most recent and least recent date

[dmimp]

Hello,

I export event sales monthly on our clients (12 tables, combined using Append). 

Each table has a "name" key column. 

From there, I have several Amount columns. 

I want to show the change (positive or negative) of a client's event spend from the first day they book to the close of their contract.

Here's what I attempted before I got stuck:

PROBLEM 1:

1. Created a Matrix table (to consolidate multiple rows from different export dates on the same persons.

• Rows: "Name"
• Columns: "Export Date": Month
• Values: Max of "Event Actual"  (event actual is the $$ amount)

In the screenshot, you can see that our first client (Aby) has a different amount each month.  I want to know what the LATEST date's amount is, minus the EARLIEST date's amount (positive or negative).

Caveats:

1. I can't just use standard deviation, because that only shows the max - min.  I want to know latest - earliest
2. different events book and close on different dates - so while this shows amounts Jan - Dec, some events have different earliest and latest dates.
3. I want to know this info per event/client, not for the overall business
4. I tried using these two posts (one, two), but neither's calculation words were accepted (such as "INFO") by Power BI or Power Query (I tried in both places).

PROBLEM 2: 

The months look like they are displaying in order , but in reality, they span from May 2024 - April 2025....but if I add the "year" to the matrix, I lose the month visualization:  

Conclusion/ask:

1. hoping you can help me with a new column or measure formula to pull out the last - first amounts.  

1.1 hoping you can tell me if I'm using the wrong visualization tool to do this?

2. hoping you can help me with the dates?

ALTERNATIVE: I am exploring Power BI, because I was previously successful in performing these calculations in Excel using Combination Query & Pivot Table. I used Calculated Item to compute the first/last for each event using the below formula.  However, Calculate Item has a character limit, and I have hit it, so I'm exploring Power BI.  But, maybe you're a wizard and have a better Pivottable Calculated Item formula?

=IFERROR(IF(May_24>0,Apr_25-May_24,IF(Jul_24>0,Apr_25-July_24,IF(Sep_24>0,Apr_25-Sep_24, IF(Oct_24>0,Apr_25-Oct_24, IF(Nov_24>0,Apr_25-Nov_24, IF(Jan_25>0,Apr_25-Jan_25,  IF(Feb_25>0,Apr_25-Feb_25, IF(Mar_25>0,Apr_25-Mar_25, 0)))))))),0)

-----------------

UPDATE: example data file.  Guide to file:

This was my original data, and table I worked with before the need to add to Power BI.  It works perfectly, BUT, the Calculated Item formula field has run out of space, so it is officially "full" and I can't add any more data to it.

Comparison Data tab.  This is the pivottable output of my PowerQuery on all subsequent tabs.

GOAL: Show "Upsell Total" on EACH ROW and OVERALL, which is the change in first contracted Total amount to final invoice Total amount per each "Name" (Contract).   The sheet also shows the change for each subcategory within the contract.  

Think of a contractor providing a bid in May 2024 of $100,000.  Then the work is complete in February 2025, and the final bill is $120,000.  The "Upsell Total" would be $20,000.   

Or if the final invoice was $98,000, then the "Upsell Total" would be -$2,000.

Each data tab and column set is a new export of the same (and some new) contracts, approximately monthly.

The "Difference" (green) column set is a Pivot Table calculated item.  The formula is set to evaluate IF a contract started in X Export (earliest), then subtract the most recent export (latest).  However, the Calculated Item field is maxed out on characters, and I can't add any more spreadsheets.

Next Steps:

I created a Merge file (Merge1) within Power PI of each of the exported-data tabs.

I tried to create a table to show each Contract's total amount on each export.

I can't figure out how to show the difference between the FIRST CONTRACTED AMOUNT and the LAST CONTRACTED AMOUNT for each contract.

I can't use Variance because it would only show highest-lowest amount (and I need latest - earliest amount).

Please also note that in the Matrix, the months are NOT in order, since some are 2024 and some are 2025.

I just want to show each contract's total change from earliest contract date to latest contract dateon the "Event Actual" field.

[v-pgoloju]

Hi @dmimp,

Thank you for reaching out to the Microsoft Fabric Forum Community.

Try below DAX to get the desired result

Upsell Total =
VAR SelectedName = SELECTEDVALUE(Merge1[Name])

VAR FirstEvent =
CALCULATE(
MAX(Merge1[Event Actual]),
TOPN(1,FILTER(Merge1, Merge1[Name] = SelectedName &&
NOT(ISBLANK(Merge1[Event Actual]))),Merge1[Export Date], ASC))

VAR LastEvent =
CALCULATE(
MAX(Merge1[Event Actual]),
TOPN(1,FILTER( Merge1, Merge1[Name] = SelectedName && NOT(ISBLANK(Merge1[Event Actual]))),Merge1[Export Date], DESC))

RETURN
LastEvent - FirstEvent

If you find this response helpful, please consider marking it as the accepted solution and giving it a thumbs-up to support others in the community.

Thanks & Regards,
Prasanna Kumar

[dmimp · Accepted solution]

Small edits and it worked!  Thank you SO MUCH!!!!

------

UpsellTotal =

VAR SelectedName = SELECTEDVALUE('Merge1'[Name])

VAR FirstEvent =

CALCULATE(

MAX('Merge1'[Event Actual]),

TOPN(1,FILTER('Merge1', 'Merged Data (2)'[Name] = SelectedName &&

NOT(ISBLANK('Merge1'[Event Actual]))),'Merge1'[Export Date].[Date], ASC))

VAR LastEvent =

CALCULATE(

MAX('Merge1'[Event Actual]),

TOPN(1,FILTER('Merge1', 'Merge1'[Name] = SelectedName && NOT(ISBLANK('Merge1'[Event Actual]))),'Merge1'[Export Date].[Date], DESC))

RETURN

LastEvent - FirstEvent

[v-pgoloju]

Hi @dmimp,

Thank you for reaching out to the Microsoft Fabric Forum Community.

.

Could you please share a sample of your data in text or table format , along with the expected outcome based on that sample?

Thank you & best regards,
Prasanna Kumar

[danextian]

Hi @dmimp 

Please provide a workable sample data (not an image), your expected result from the same sample data and your reasoning behind. You may post a link to Excel or a sanitized copy of your PBIX stored in the cloud. While providing sample data can be time-consuming, but so is coming up with a solution.

[dmimp]

Thank you (everyone) for your fast replies!  I've been working for a couple of hours on cleaning the data so that it can be public, but everything I dokeeps breaking the links, tables, calculations, etc.  Then when I think I've fixed it, the formulas aren't providing correct results.   I'll keep working on it again as soon as I can, and post ASAP.  I appreciate you all!

[Ashish_Excel]

Hi,

Share the download link of the PBI file.