Extract period values from a cumulative total column in a data table

[CailenHutson]

I'm trying to extract period value from a date snapshot dataset.  The table looks as follows.  There are a couple things to keep in mind with regard to this table.  The first is that the Column CostItemID is on the many side of a one-to-many relationship.  The second is that the date stamp column is linked to a date table. The very last column in the table is the value I'm trying to extract from the table.  Note that it is possible to periods there the cost of associated with a cost item can decrease in a given period.

CostItemID	Date Stamp	Project cost to date	Cost incurred from previous date stamp
1	5/8/20	100
2	5/8/20	200
3	5/8/20	300
1	5/9/20	150	50
2	5/9/20	275	75
3	5/9/20	365	65
1	5/10/20	175	25
2	5/10/20	225	-50
3	5/10/20	375	10

[CailenHutson]

@Ashish_Mathur & @mahoneypat Thank you both for singing!!! You may have to be patient with me, I'm just scratching the surface when it comes to Dax. I tried both methods and didn't seem to get the desired result. It probably should have been clearer about the frequency of each date stamp. The new data is added to the table weekly with a respective date stamp. I'm not sure if this would affect any of your expressions. I found a clumsy but effective solution with PowerQuery, but I'm afraid this will tie the updates since this dataset has 7M records. If we can find a way to do it with dax it should greatly improve performance. Below is a link to the video I've found (it can also provide some clarity around what I'm trying to do). You can probably hit 4:30 on the video if you want. I thought it was a pretty smart job.

A couple of things to keep in mind:

1. Ignore the author who creates a user table. I already have it in the data model.

2. Also, do not consider merging the user IDs in the table. The dataset I'm working with already comes with those values (CostItemID)

https://youtu.be/152tPJFu-A8

Again, thank you both for looking at this. It was kind of a head scratching the rabbit hole for me.

Sincerely

Cailen

[CailenHutson · Accepted solution]

Thank you both for helping with this challenge. I'm sure there's a better way than what I came up with. However, after some research on the Internet I was able to come up with a solution that seems to give me the desired result and performs decently. What I came up with is this:

1. In Power Query sort CostItemID in ascending.

2. The next step sorts DateStamp in ascending.

3. Then add an index column.

4. After closing and applying I could arrive at a measurement as follows:

Cost of the period ?

where previousrowcost ?

CALCULATE(SUM('Data'[CostToDate]),

FILTER(ALL('Data'),

SUMX(

FILTER('Data', 'Data'[Index] ? EARLIER('Data'[Index]) + 1 && 'Data'[CostItemId] ? EARLIER('Data'[CostItemId])),

'Data'[CostToDate])))

was currentrowcost on SUM('Data'[CostToDate])

return

IF(ISBLANK(previousrowcost),BLANK(), currentrowcost - previouserowcost)

[CailenHutson]

@Ashish_Mathur & @mahoneypat  Thank you both for chiming in!!!  You may have to be patient with me, I'm just scratching the surface when it comes to dax.  I tried both methods and didn't seem to get the desired result.  I probably should have been more clear with regard to the frequency of each date stamp.  New data is added to the table on a weekly basis with a respective date stamp.  I'm not sure if this would affect either of your expressions.  I did find a clunky yet effective solution with PowerQuery but I'm afraid this will bog down refreshes given that this dataset has 7M records.  If we can figure out a way to do it with dax it should greatly improve performance.  Below is a link to the video I have found (it may also provide some clarity around what I'm trying to do).  You can probably skip to 4:30 in the video if you wish.  I did find it to be a rather clever work around. 

A couple things to note:

1. Disregard the author creating a user table.  I already have that in the data model. 

2. Also, disregard the merger of User ID's to the table.  The dataset I'm working with already comes with those values (CostItemID)

https://youtu.be/152tPJFu-A8

Once again, thank you both for looking at this.  Its been kind of a head scratching rabbit hole for me.

Sincerely,

Cailen

[mahoneypat]

If you add these two calculated columns to the sample data table you provided, you can get the desired result:

ItemSequence = var currentitem = Cost[CostItemID]

var currentdatestamp = Cost[Date Stamp]

var result = CALCULATE(COUNTROWS(Cost), all(Cost), Cost[CostItemID]=currentitem, Cost[Date Stamp]<= currentdatestamp)

return result

PeriodDifference = var currentvalue = Cost[Project cost to date]

var currentitemsequence = Cost[ItemSequence]

var currentitem = Cost[CostItemID]

var previousvalue = CALCULATE(MIN(Cost[Project cost to date]),ALL(Cost), Cost[ItemSequence]=currentitemsequence-1, Cost[CostItemID]=currentitem)

return if(ISBLANK(previousvalue), BLANK(), currentvalue-previousvalue)

You can then add a measure using the new PeriodDifference column.  From the video, I am assuming you were looking for a column, not a measure.

If this solution works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Ashish_Mathur]

Hi,

For every CostItemID, if there will always be continuous dates in the Date Stamp field, then these measures will work

Cost to Date =SUM(Data[Project cost to Date])

Incremental cost = [Cost to Date]-CALCULATE([Cost to Date],PREVIOUSDAY(Calendar[Date]))

Hope this helps.

[mahoneypat]

Change From Previous = var currentday = selectedvalue(Date[Date])

var todayvalue = sum(Table[Project Cost to Date])

var prevdatevalue = calculate(sum(Table[Project Cost to Date]), all(Date[Date]), Date[Date] = currentday -1)
return todayvalue - prevdatevalue

This assumes you have results everyday (i.e., prevdatevalue always has a value), and that your visual is at the day and individual item granularity.

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat