Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
CailenHutson
Frequent Visitor

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

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.

 

CostItemIDDate StampProject cost to dateCost incurred from previous date stamp
15/8/20100 
25/8/20200 
35/8/20300 
15/9/2015050
25/9/2027575
35/9/2036565
15/10/2017525
25/10/20225-50
35/10/2037510
1 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)

View solution in original post

6 REPLIES 6
CailenHutson
Frequent Visitor

@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

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
Frequent Visitor

@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

 

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
 




Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
mahoneypat
Employee
Employee

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.