Calculate isolated values from accumulated values

[Anonymous]

Hello all,

I have a data table where the values are only stored cumulatively per day and I would like to derive the isolated value from the last previous date.

Additionally, there is a distribution channel column that also needs to be included in the consideration, as only the values within that of the same distribution channel should be determined. Unfortunately, to make it even more complex, the date is not consecutive or periodic.

Attached is my sample data.

Date	Product	distribution channel	Value
01.01.2023	4711	1	1000
01.01.2023	4712	1	500
01.01.2023	4711	2	500
01.01.2023	4711	2	1200
01.01.2023	4712	2	600
05.01.2023	4711	1	1500
05.01.2023	4712	1	750
05.01.2023	4711	2	600
05.01.2023	4711	2	1800
05.01.2023	4712	2	600
07.01.2023	4711	1	1500
07.01.2023	4712	1	1000
07.01.2023	4711	2	800
07.01.2023	4711	2	1800
07.01.2023	4712	2	600

Do you have any idea how I can best implement this

Many greetings and thanks

[ERD]

@Anonymous , here is a measure:

isolated value =
VAR prev_v =
    CALCULATE (
        MAX ( Table_[Value] ),
        ALL ( Table_ ),
        Table_[Date] < SELECTEDVALUE ( Table_[Date] ),
        Table_[Product] = SELECTEDVALUE ( Table_[Product] ),
        Table_[distribution channel] = SELECTEDVALUE ( Table_[distribution channel] )
    )
RETURN
    SELECTEDVALUE ( Table_[Value] ) - prev_v

[Anonymous]

Hi ERD, 

is it possible to create the data not alls Mesure but as column, unfortunately I get then no value back

THX

Nick

[ERD]

@Anonymous , yes,

Column = 
VAR dt = Table_[Date]
VAR pr = Table_[Product]
VAR dc = Table_[distribution channel]
VAR prev_v =
    CALCULATE (
        MAX ( Table_[Value] ),
        ALL ( Table_ ),
        Table_[Date] < dt,
        Table_[Product] = pr,
        Table_[distribution channel] = dc
    )
RETURN
    Table_[Value] - prev_v

[Anonymous]

thank you very much, that looks very good, one question still, what would I have to change so that on lower values are considered.

As an example, I have stock that has receipts and issues and so the previous value can also be higher.

Thanks Nick

[ERD]

Hi. I'm afraid I don't get the question. Values can be different. The code takes the previous one by the date and then calculates the difference.

[Anonymous]

sorry for the late feedback, it always goes to the maximum. This works great if only values are added in the accumulation and thus the value always increases.

I have unfortunately another variant, there can also be a stock reduction. So that is not always the highest value, the last

Do you have an idea for this?

Thanks for your support, I learn a lot from it!

Many greetings
Nick

[ERD]

It would be easier if you provide a data sample with these cases and the required result as a separate column.

[Anonymous]

Hello ERD,

thank you for taking so much time for me.
I have built up the data table again, you are right, so you can see it better.

I have known data:
Date, Product, Location

The value I am looking for is the difference from the last date.

Because goods receipt and goods issue are unfortunately not included in the data.

Known data	Known data	Known data	Known data	not in Data	not in Data	value searched
Date	Product	Location	Value	goods receipt	outgoing goods	Output
01.01.2023	4711	WE1	10			10
06.01.2023	4711	WE1	12	5	-3	2
10.01.2023	4711	WE1	8	1	-5	-4
25.01.2023	4711	WE1	6	3	-5	-2
01.02.2023	4711	WE1	5	1	-2	-1
05.02.2023	4711	WE1	10	10	-5	5
08.02.2023	4711	WE1	5	0	-5	-5
01.01.2023	4711	WE1	15	12	-2	10
06.01.2023	4711	WE1	15	3	-3	0
13.01.2023	4711	WE1	8	1	-8	-7
18.01.2023	4711	WE1	11	8	-5	3

Many greetings
Sebastian

[ERD]

@Anonymous , it's a bit weird output taking into account there's no sorting on the date level. Anyway, to achieve the result you've shown you need to create an Index column. It's very easy in Power Query:

The your column:

Column = 
VAR ind = 'Table'[Index]
VAR pr = 'Table'[Product]
VAR loc = 'Table'[Location]
VAR vl = 'Table'[Value]
VAR prev_index = CALCULATE ( MAX ( 'Table'[Index] ), ALL ( 'Table' ), 'Table'[Index] < ind )
VAR prev_amt =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        ALL ( 'Table' ),
        'Table'[Index] = prev_index,
        'Table'[Product] = pr,
        'Table'[Location] = loc
    )
RETURN
    vl - prev_amt

[DimaMD]

@Anonymous  hi, what is your desired outcome?

[Anonymous]

Hi DimaMD,

this is my Outcome

Date	Product	distribution channel	Value	Outcome
01.01.2023	4711	1	1000	1000
01.01.2023	4712	1	500	500
01.01.2023	4711	2	500	500
01.01.2023	4712	2	600	600
05.01.2023	4711	1	1500	500
05.01.2023	4712	1	750	250
05.01.2023	4711	2	600	100
05.01.2023	4712	2	600	0
07.01.2023	4711	1	1500	0
07.01.2023	4712	1	1000	250
07.01.2023	4711	2	800	200
07.01.2023	4712	2	600	0