I have a sales history table where all (relevant) changes to sales are made as they progress through the pipeline.
For the purposes of this, the key fields are the timestamp the last change was made (datetime), the current stage in the pipeline, and the current value. What I’m trying to get to is to add the most recent values together for whatever date range I’m provided with. Here’s my current result
Here’s a snippet of the data
And here’s the data model
At any level of date granularity, I want to only consider the most recent transaction for the date and use the pipeline stage and value for all calculations.
This dax figures out when that most recent transaction was:
LastTransaction = CALCULATE(max(deals[Index]),REMOVEFILTERS((tblStage)))
Here’s the dax that created the first picture
var transactionID = [LastTransaction]
var dealFilter = filter(deals,deals[Index]=transactionID)
I can see from the result that I nearly have what I want, but I can’t for the life of me figure out how to get the totalling working for a deals that aren’t the final one for the period.
I think I need to get the last transaction for each unique deal and only use those in the calculations - and I suspect I’m making a really simple error in my logic. I’ve only been using BI for a couple of months, and am new to thinking about data in this way - if I could write a SQL query, it’d be easy!
Any help would be greatly appreciated.
Edited to include a pbix with all the datatestTimeseries.pbix (121.8 KB)