topic Re: Calculate isolated values from accumulated values in DAX Commands and Tips

Calculate isolated values from accumulated values

Anonymous — Thu, 31 Aug 2023 12:21:16 GMT

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

Re: Calculate isolated values from accumulated values

DimaMD — Thu, 31 Aug 2023 13:10:20 GMT

Anonymous hi, what is your desired outcome?

Re: Calculate isolated values from accumulated values

Anonymous — Thu, 31 Aug 2023 13:57:55 GMT

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

Re: Calculate isolated values from accumulated values

ERD — Thu, 31 Aug 2023 16:47:38 GMT

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

Re: Calculate isolated values from accumulated values

Anonymous — Sat, 02 Sep 2023 09:14:26 GMT

Hi ERD,

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

THX

Nick

Re: Calculate isolated values from accumulated values

ERD — Mon, 04 Sep 2023 05:35:29 GMT

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

Re: Calculate isolated values from accumulated values

Anonymous — Mon, 04 Sep 2023 15:03:46 GMT

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

Re: Calculate isolated values from accumulated values

ERD — Mon, 04 Sep 2023 17:52:51 GMT

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.

Re: Calculate isolated values from accumulated values

Anonymous — Wed, 06 Sep 2023 11:46:41 GMT

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

Re: Calculate isolated values from accumulated values

ERD — Wed, 06 Sep 2023 15:46:28 GMT

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

Re: Calculate isolated values from accumulated values

Anonymous — Thu, 07 Sep 2023 07:05:20 GMT

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

Re: Calculate isolated values from accumulated values

ERD — Thu, 07 Sep 2023 09:44:08 GMT

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