Subtracting Values from the same column into new column based on dates

[Evan_Power_Bi]

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

[Anonymous · Accepted solution]

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
    _diff

The PBIX file is attached for reference.

Best Regards,
Gao
Community Support Team

If 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]

@Evan_Power_Bi You need to share your pbix file, maybe remove sensitive data before sharing.

[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!

[Evan_Power_Bi]

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 can you share your measure?

[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!

[Evan_Power_Bi]

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

[Evan_Power_Bi]

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

[parry2k]

@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!