I have a table that I add a new snapshot of data to monthly. The date the new data is entered is the Snapshot Date column (Screenshot - Forecasted Data Table attached). I would like to calculate the difference in the column 'Recognized + Projected Revenue (CAD)' between Snapshots. For Example: I would like the Difference in 'Recognized + Projected Revenue (CAD)' in 'Snapshot Date' "May 2016" and 'Snapshot Date' "June 2016" for project = "X" at 'Date' = "July 2016".
I have also attached a picture of how I have configured my Matrix that is displaying the data.
Any help would be very appreciated.
1 ACCEPTED SOLUTION
You can do it with several slicers and tables as below. We only need to create a column and measure with following DAX formula.
I’ve also upload my .pbix file here for reference.
Date YearMonth = FORMAT ( 'Forecasted Data'[Date], "mmmm yyyy" )
Diff =
CALCULATE (
SUM ( 'Forecasted Data'[Recognized + Projected Revenue (CAD)] ),
FILTER (
'Forecasted Data',
'Forecasted Data'[Snapshot Date] = MAX ( 'Forecasted Data'[Snapshot Date] )
)
)
- CALCULATE (
SUM ( 'Forecasted Data'[Recognized + Projected Revenue (CAD)] ),
FILTER (
'Forecasted Data',
'Forecasted Data'[Snapshot Date] = MIN ( 'Forecasted Data'[Snapshot Date] )
)
)
Best Regards,
Herbert
You can do it with several slicers and tables as below. We only need to create a column and measure with following DAX formula.
I’ve also upload my .pbix file here for reference.
Date YearMonth = FORMAT ( 'Forecasted Data'[Date], "mmmm yyyy" )
Diff =
CALCULATE (
SUM ( 'Forecasted Data'[Recognized + Projected Revenue (CAD)] ),
FILTER (
'Forecasted Data',
'Forecasted Data'[Snapshot Date] = MAX ( 'Forecasted Data'[Snapshot Date] )
)
)
- CALCULATE (
SUM ( 'Forecasted Data'[Recognized + Projected Revenue (CAD)] ),
FILTER (
'Forecasted Data',
'Forecasted Data'[Snapshot Date] = MIN ( 'Forecasted Data'[Snapshot Date] )
)
)
Best Regards,
Herbert
Can you provide some sample data and information on expected output that we could use to engineer a solution?
Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!: DAX For Humans
DAX is easy, CALCULATE makes DAX hard...
Hello,
I have attached a screenshot of a sample ot put table, I have a matrix formatted like this table, just missing the differnce column. I have also pasted in a sample of the table I am using for the raw data.
| Snapshot Date | Date | Company | Project | Project Client | Project Project stage | Project Billable Project | Project Customer Contract Type | Project First booking date | Resources - Booked hours with actuals | Recognized + Projected Revenue (CAD) | Projected cost [primary loaded cost] (CAD) | Actual + Projected Margin (CAD) |
| 5/23/2016 | 5/1/2016 | Test Company | Project A | Client AVA | Active | Billable | Firm Fixed Price | 12/1/2016 | 103 | 26500 | 0 | 0 |
| 5/23/2016 | 6/1/2016 | Test Company | Project A | Client AVA | Active | Billable | Firm Fixed Price | 12/1/2016 | 112 | 30000 | 0 | 0 |
| 5/23/2016 | 7/1/2016 | Test Company | Project A | Client AVA | Active | Billable | Firm Fixed Price | 12/1/2016 | 95 | 33000 | 0 | 0 |
| 5/23/2016 | 8/1/2016 | Test Company | Project A | Client AVA | Active | Billable | Firm Fixed Price | 12/1/2016 | 130 | 12500 | 0 | 0 |
| 5/23/2016 | 9/1/2016 | Test Company | Project A | Client AVA | Active | Billable | Firm Fixed Price | 12/1/2016 | 106 | 25000 | 0 | 0 |
| 5/23/2016 | 10/1/2016 | Test Company | Project A | Client AVA | Active | Billable | Firm Fixed Price | 12/1/2016 | 125 | 35000 | 0 | 0 |
| 5/23/2016 | 11/1/2016 | Test Company | Project A | Client AVA | Active | Billable | Firm Fixed Price | 12/1/2016 | 102 | 55000 | 0 | 0 |
| 5/23/2016 | 12/1/2016 | Test Company | Project A | Client AVA | Active | Billable | Firm Fixed Price | 12/1/2016 | 109 | 23400 | 0 | 0 |
| 5/23/2016 | 1/1/2017 | Test Company | Project A | Client AVA | Active | Billable | Firm Fixed Price | 12/1/2016 | 0 | 0 | 0 | 0 |
| 5/23/2016 | 2/1/2017 | Test Company | Project A | Client AVA | Active | Billable | Firm Fixed Price | 12/1/2016 | 0 | 0 | 0 | 0 |
| 5/23/2016 | 3/1/2017 | Test Company | Project A | Client AVA | Active | Billable | Firm Fixed Price | 12/1/2016 | 0 | 0 | 0 | 0 |
| 5/23/2016 | 4/1/2017 | Test Company | Project A | Client AVA | Active | Billable | Firm Fixed Price | 12/1/2016 | 0 | 0 | 0 | 0 |
| 5/23/2016 | 5/1/2017 | Test Company | Project A | Client AVA | Active | Billable | Firm Fixed Price | 12/1/2016 | 0 | 0 | 0 | 0 |
| 5/23/2016 | 6/1/2017 | Test Company | Project A | Client AVA | Active | Billable | Firm Fixed Price | 12/1/2016 | 0 | 0 | 0 | 0 |
| 5/23/2016 | 7/1/2017 | Test Company | Project A | Client AVA | Active | Billable | Firm Fixed Price | 12/1/2016 | 0 | 0 | 0 | 0 |
| 5/23/2016 | 8/1/2017 | Test Company | Project A | Client AVA | Active | Billable | Firm Fixed Price | 12/1/2016 | 0 | 0 | 0 | 0 |
| 5/23/2016 | 9/1/2017 | Test Company | Project A | Client AVA | Active | Billable | Firm Fixed Price | 12/1/2016 | 0 | 0 | 0 | 0 |
| 5/23/2016 | 10/1/2017 | Test Company | Project A | Client AVA | Active | Billable | Firm Fixed Price | 12/1/2016 | 0 | 0 | 0 | 0 |
| 5/23/2016 | 11/1/2017 | Test Company | Project A | Client AVA | Active | Billable | Firm Fixed Price | 12/1/2016 | 0 | 0 | 0 | 0 |
| 5/23/2016 | 12/1/2017 | Test Company | Project A | Client AVA | Active | Billable | Firm Fixed Price | 12/1/2016 | 0 | 0 | 0 | 0 |
| 5/23/2016 | 1/1/2018 | Test Company | Project A | Client AVA | Active | Billable | Firm Fixed Price | 12/1/2016 | 0 | 0 | 0 | 0 |
| 5/23/2016 | 2/1/2018 | Test Company | Project A | Client AVA | Active | Billable | Firm Fixed Price | 12/1/2016 | 0 | 0 | 0 | 0 |
| 5/23/2016 | 3/1/2018 | Test Company | Project A | Client AVA | Active | Billable | Firm Fixed Price | 12/1/2016 | 0 | 0 | 0 | 0 |
| 5/23/2016 | 4/1/2018 | Test Company | Project A | Client AVA | Active | Billable | Firm Fixed Price | 12/1/2016 | 0 | 0 | 0 | 0 |
| 5/23/2016 | 5/1/2018 | Test Company | Project A | Client AVA | Active | Billable | Firm Fixed Price | 12/1/2016 | 0 | 0 | 0 | 0 |
| 6/23/2016 | 5/1/2016 | Test Company | Project A | Client AVA | Active | Billable | Firm Fixed Price | 12/1/2016 | 100 | 25000 | 0 | 0 |
| 6/23/2016 | 6/1/2016 | Test Company | Project A | Client AVA | Active | Billable | Firm Fixed Price | 12/1/2016 | 102 | 25500 | 0 | 0 |
| 6/23/2016 | 7/1/2016 | Test Company | Project A | Client AVA | Active | Billable | Firm Fixed Price | 12/1/2016 | 105 | 24500 | 0 | 0 |
| 6/23/2016 | 8/1/2016 | Test Company | Project A | Client AVA | Active | Billable | Firm Fixed Price | 12/1/2016 | 107 | 23400 | 0 | 0 |
| 6/23/2016 | 9/1/2016 | Test Company | Project A | Client AVA | Active | Billable | Firm Fixed Price | 12/1/2016 | 100 | 45000 | 0 | 0 |
| 6/23/2016 | 10/1/2016 | Test Company | Project A | Client AVA | Active | Billable | Firm Fixed Price | 12/1/2016 | 102 | 55000 | 0 | 0 |
| 6/23/2016 | 11/1/2016 | Test Company | Project A | Client AVA | Active | Billable | Firm Fixed Price | 12/1/2016 | 105 | 65000 | 0 | 0 |
| 6/23/2016 | 12/1/2016 | Test Company | Project A | Client AVA | Active | Billable | Firm Fixed Price | 12/1/2016 | 107 | 23400 | 0 | 0 |
| 6/23/2016 | 1/1/2017 | Test Company | Project A | Client AVA | Active | Billable | Firm Fixed Price | 12/1/2016 | 0 | 0 | 0 | 0 |
| 6/23/2016 | 2/1/2017 | Test Company | Project A | Client AVA | Active | Billable | Firm Fixed Price | 12/1/2016 | 0 | 0 | 0 | 0 |
| 6/23/2016 | 3/1/2017 | Test Company | Project A | Client AVA | Active | Billable | Firm Fixed Price | 12/1/2016 | 0 | 0 | 0 | 0 |
| 6/23/2016 | 4/1/2017 | Test Company | Project A | Client AVA | Active | Billable | Firm Fixed Price | 12/1/2016 | 0 | 0 | 0 | 0 |
| 6/23/2016 | 5/1/2017 | Test Company | Project A | Client AVA | Active | Billable | Firm Fixed Price | 12/1/2016 | 0 | 0 | 0 | 0 |
| 6/23/2016 | 6/1/2017 | Test Company | Project A | Client AVA | Active | Billable | Firm Fixed Price | 12/1/2016 | 0 | 0 | 0 | 0 |
| 6/23/2016 | 7/1/2017 | Test Company | Project A | Client AVA | Active | Billable | Firm Fixed Price | 12/1/2016 | 0 | 0 | 0 | 0 |
| 6/23/2016 | 8/1/2017 | Test Company | Project A | Client AVA | Active | Billable | Firm Fixed Price | 12/1/2016 | 0 | 0 | 0 | 0 |
| 6/23/2016 | 9/1/2017 | Test Company | Project A | Client AVA | Active | Billable | Firm Fixed Price | 12/1/2016 | 0 | 0 | 0 | 0 |
| 6/23/2016 | 10/1/2017 | Test Company | Project A | Client AVA | Active | Billable | Firm Fixed Price | 12/1/2016 | 0 | 0 | 0 | 0 |
| 6/23/2016 | 11/1/2017 | Test Company | Project A | Client AVA | Active | Billable | Firm Fixed Price | 12/1/2016 | 0 | 0 | 0 | 0 |
| 6/23/2016 | 12/1/2017 | Test Company | Project A | Client AVA | Active | Billable | Firm Fixed Price | 12/1/2016 | 0 | 0 | 0 | 0 |
| 6/23/2016 | 1/1/2018 | Test Company | Project A | Client AVA | Active | Billable | Firm Fixed Price | 12/1/2016 | 0 | 0 | 0 | 0 |
| 6/23/2016 | 2/1/2018 | Test Company | Project A | Client AVA | Active | Billable | Firm Fixed Price | 12/1/2016 | 0 | 0 | 0 | 0 |
| 6/23/2016 | 3/1/2018 | Test Company | Project A | Client AVA | Active | Billable | Firm Fixed Price | 12/1/2016 | 0 | 0 | 0 | 0 |
| 6/23/2016 | 4/1/2018 | Test Company | Project A | Client AVA | Active | Billable | Firm Fixed Price | 12/1/2016 | 0 | 0 | 0 | 0 |
| 6/23/2016 | 5/1/2018 | Test Company | Project A | Client AVA | Active | Billable | Firm Fixed Price | 12/1/2016 | 0 | 0 | 0 | 0 |
| 7/23/2016 | 5/1/2016 | Test Company | Project A | Client AVA | Active | Billable | Firm Fixed Price | 12/1/2016 | 107 | 26000 | 0 | 0 |
| 7/23/2016 | 6/1/2016 | Test Company | Project A | Client AVA | Active | Billable | Firm Fixed Price | 12/1/2016 | 110 | 24500 | 0 | 0 |
| 7/23/2016 | 7/1/2016 | Test Company | Project A | Client AVA | Active | Billable | Firm Fixed Price | 12/1/2016 | 98 | 23000 | 0 | 0 |
| 7/23/2016 | 8/1/2016 | Test Company | Project A | Client AVA | Active | Billable | Firm Fixed Price | 12/1/2016 | 100 | 5000 | 0 | 0 |
| 7/23/2016 | 9/1/2016 | Test Company | Project A | Client AVA | Active | Billable | Firm Fixed Price | 12/1/2016 | 102 | 25000 | 0 | 0 |
| 7/23/2016 | 10/1/2016 | Test Company | Project A | Client AVA | Active | Billable | Firm Fixed Price | 12/1/2016 | 102 | 35000 | 0 | 0 |
| 7/23/2016 | 11/1/2016 | Test Company | Project A | Client AVA | Active | Billable | Firm Fixed Price | 12/1/2016 | 105 | 60000 | 0 | 0 |
| 7/23/2016 | 12/1/2016 | Test Company | Project A | Client AVA | Active | Billable | Firm Fixed Price | 12/1/2016 | 107 | 23400 | 0 | 0 |
| 7/23/2016 | 1/1/2017 | Test Company | Project A | Client AVA | Active | Billable | Firm Fixed Price | 12/1/2016 | 0 | 0 | 0 | 0 |
| 7/23/2016 | 2/1/2017 | Test Company | Project A | Client AVA | Active | Billable | Firm Fixed Price | 12/1/2016 | 0 | 0 | 0 | 0 |
| 7/23/2016 | 3/1/2017 | Test Company | Project A | Client AVA | Active | Billable | Firm Fixed Price | 12/1/2016 | 0 | 0 | 0 | 0 |
| 7/23/2016 | 4/1/2017 | Test Company | Project A | Client AVA | Active | Billable | Firm Fixed Price | 12/1/2016 | 0 | 0 | 0 | 0 |
| 7/23/2016 | 5/1/2017 | Test Company | Project A | Client AVA | Active | Billable | Firm Fixed Price | 12/1/2016 | 0 | 0 | 0 | 0 |
| 7/23/2016 | 6/1/2017 | Test Company | Project A | Client AVA | Active | Billable | Firm Fixed Price | 12/1/2016 | 0 | 0 | 0 | 0 |
| 7/23/2016 | 7/1/2017 | Test Company | Project A | Client AVA | Active | Billable | Firm Fixed Price | 12/1/2016 | 0 | 0 | 0 | 0 |
| 7/23/2016 | 8/1/2017 | Test Company | Project A | Client AVA | Active | Billable | Firm Fixed Price | 12/1/2016 | 0 | 0 | 0 | 0 |
| 7/23/2016 | 9/1/2017 | Test Company | Project A | Client AVA | Active | Billable | Firm Fixed Price | 12/1/2016 | 0 | 0 | 0 | 0 |
| 7/23/2016 | 10/1/2017 | Test Company | Project A | Client AVA | Active | Billable | Firm Fixed Price | 12/1/2016 | 0 | 0 | 0 | 0 |
| 7/23/2016 | 11/1/2017 | Test Company | Project A | Client AVA | Active | Billable | Firm Fixed Price | 12/1/2016 | 0 | 0 | 0 | 0 |
| 7/23/2016 | 12/1/2017 | Test Company | Project A | Client AVA | Active | Billable | Firm Fixed Price | 12/1/2016 | 0 | 0 | 0 | 0 |
| 7/23/2016 | 1/1/2018 | Test Company | Project A | Client AVA | Active | Billable | Firm Fixed Price | 12/1/2016 | 0 | 0 | 0 | 0 |
| 7/23/2016 | 2/1/2018 | Test Company | Project A | Client AVA | Active | Billable | Firm Fixed Price | 12/1/2016 | 0 | 0 | 0 | 0 |
| 7/23/2016 | 3/1/2018 | Test Company | Project A | Client AVA | Active | Billable | Firm Fixed Price | 12/1/2016 | 0 | 0 | 0 | 0 |
| 7/23/2016 | 4/1/2018 | Test Company | Project A | Client AVA | Active | Billable | Firm Fixed Price | 12/1/2016 | 0 | 0 | 0 | 0 |
| 7/23/2016 | 5/1/2018 | Test Company | Project A | Client AVA | Active | Billable | Firm Fixed Price | 12/1/2016 | 0 | 0 | 0 | 0 |
Helpful resources
FabCon Global Hackathon
Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!
Power BI Monthly Update - October 2025
Check out the October 2025 Power BI update to learn about new features.
FabCon Atlanta 2026
Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
