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?
@ 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!: The Definitive Guide to Power Query (M)
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
Microsoft Fabric Learn Together
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Power BI Monthly Update - April 2024
Check out the April 2024 Power BI update to learn about new features.
Fabric Community Update - April 2024
Find out what's new and trending in the Fabric Community.
