Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
@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
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.
Check out my latest demo report in the data story gallery.
Stand with Ukraine!
Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/
Thank you!
Hi ERD,
is it possible to create the data not alls Mesure but as column, unfortunately I get then no value back
THX
Nick
@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
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.
Check out my latest demo report in the data story gallery.
Stand with Ukraine!
Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/
Thank you!
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
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.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.
Check out my latest demo report in the data story gallery.
Stand with Ukraine!
Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/
Thank you!
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
It would be easier if you provide a data sample with these cases and the required result as a separate column.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.
Check out my latest demo report in the data story gallery.
Stand with Ukraine!
Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/
Thank you!
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
@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
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.
Check out my latest demo report in the data story gallery.
Stand with Ukraine!
Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/
Thank you!
@Anonymous hi, what is your desired outcome?
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 |
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
24 | |
10 | |
6 | |
6 | |
6 |
User | Count |
---|---|
29 | |
11 | |
11 | |
10 | |
6 |