Forum Discussion
Subtracting Values from the same column into new column based on dates
I have
Date | Material | Last Week Date | S | F |
1/8/2022 iron 1/1/2022 1.00 .99
1/15/2022 iron 1/8/2022 1.10 1.01
Based on the date and last week's date i want to take S and subtract it by last weeks date to get the difference. I was trying to use the video down below which i have used before but for some reason i cannot get it to work. I do not really need the "Last Week Date" column it was added as an index function like in the video. ALSO, very important note is that there are multiple materials in the Material column and if material is not distingushed it will add/subtract everything from that date range.
Calculate difference between two rows in Power BI - Bing video
Hi Evan_Power_Bi ,
Having already seen the video, if any material corresponds to a unique date and your expected output is such, try this calculation column.
Diff_S = VAR _date = 'Table'[Date] VAR _material = 'Table'[Material] VAR _cur_s = CALCULATE ( MAX ( 'Table'[S] ), FILTER ( 'Table', 'Table'[Date] = _date && 'Table'[Material] = _material ) ) VAR _pre_date = CALCULATE ( MAX ( 'Table'[Date] ), FILTER ( 'Table', 'Table'[Material] = _material && 'Table'[Date] < _date ) ) VAR _pre_s = CALCULATE ( MAX ( 'Table'[S] ), FILTER ( 'Table', 'Table'[Date] = _pre_date && 'Table'[Material] = _material ) ) + 0 VAR _diff = IF ( _pre_s <> 0, _cur_s - _pre_s, 0 ) RETURN _diffThe PBIX file is attached for reference.
Best Regards,
Gao
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data
9 Replies
Hi Evan_Power_Bi ,
Having already seen the video, if any material corresponds to a unique date and your expected output is such, try this calculation column.
Diff_S = VAR _date = 'Table'[Date] VAR _material = 'Table'[Material] VAR _cur_s = CALCULATE ( MAX ( 'Table'[S] ), FILTER ( 'Table', 'Table'[Date] = _date && 'Table'[Material] = _material ) ) VAR _pre_date = CALCULATE ( MAX ( 'Table'[Date] ), FILTER ( 'Table', 'Table'[Material] = _material && 'Table'[Date] < _date ) ) VAR _pre_s = CALCULATE ( MAX ( 'Table'[S] ), FILTER ( 'Table', 'Table'[Date] = _pre_date && 'Table'[Material] = _material ) ) + 0 VAR _diff = IF ( _pre_s <> 0, _cur_s - _pre_s, 0 ) RETURN _diffThe PBIX file is attached for reference.
Best Regards,
Gao
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data
- parry2k
Super User
Evan_Power_Bi try this measure:
Diff from last week = VAR __material = MAX ( Table[Material] ) VAR __lastweekDate = MAX ( Table[Last Week Date] ) RETURN CALCULATE ( SUM ( Table[S] ), ALLSELECTED ( Table ), Table[Material] = __material, Table[Date] = __lastWeekDate )✨ Follow us on LinkedIn and to our YouTube channel
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
- parry2k
Evan_Power_Bi can you make sure the data type of last week date and date column is date, seems like one of the column type is text.
✨ Follow us on LinkedIn and to our YouTube channel
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
I made a simple mistake and put the wrong table and column in the wrong place. Though the column is showing the number 832 for all of the rows currently. when it should only really show a few cents like .10
So i am looking for iron at 1.00 1/8/22 minus .90 1/1/22 equals .10
1/15/22 1.15 minus 1.00 1/8/22 equals .15
- parry2k
Evan_Power_Bi can you share your measure?
- parry2k
Evan_Power_Bi I missed adding the subtraction part
Total Sales = SUM ( Table[S} ) Diff from last week = VAR __material = MAX ( Table[Material] ) VAR __lastweekDate = MAX ( Table[Last Week Date] ) VAR __prevWeek = CALCULATE ( Total Sales , ALLSELECTED ( Table ), Table[Material] = __material, Table[Date] = __lastWeekDate ) RETURN IF ( NOT ISBLANK ( __prevWeek ), [Total Sales] - __prevWeek )✨ Follow us on LinkedIn and to our YouTube channel
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
This did not work either. I am trying to find the difference between individual price per week. This is mainly taking a total and subtracting from it 😕 If you look at the video that i provided in the link it better describes what i am trying to do.
- parry2k
Evan_Power_Bi You need to share your pbix file, maybe remove sensitive data before sharing.
