Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Calculate isolated values from accumulated values

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

11 REPLIES 11
ERD
Community Champion
Community Champion

@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

ERD_0-1693500451488.png

 

 

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
Not applicable

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
Community Champion
Community Champion

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

Anonymous
Not applicable

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
Community Champion
Community Champion

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!

Anonymous
Not applicable

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
Community Champion
Community Champion

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!

Anonymous
Not applicable

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 dataKnown dataKnown dataKnown datanot in Datanot in Datavalue searched
DateProductLocationValuegoods receiptoutgoing goodsOutput
01.01.20234711WE110  10
06.01.20234711WE1125-32
10.01.20234711WE181-5-4
25.01.20234711WE163-5-2
01.02.20234711WE151-2-1
05.02.20234711WE11010-55
08.02.20234711WE150-5-5
01.01.20234711WE11512-210
06.01.20234711WE1153-30
13.01.20234711WE181-8-7
18.01.20234711WE1118-53

 

Many greetings
Sebastian

ERD
Community Champion
Community Champion

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

ERD_0-1694079799267.png

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

ERD_1-1694079840726.png

 

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!

DimaMD
Solution Sage
Solution Sage

@Anonymous  hi, what is your desired outcome?

 


__________________________________________

Thank you for your like and decision

__________________________________________

Greetings from Ukraine

To help me grow PayPal: embirddima@gmail.com
Anonymous
Not applicable

Hi DimaMD,

 

this is my Outcome

 

DateProductdistribution channelValueOutcome
01.01.20234711110001000
01.01.202347121500500
01.01.202347112500500
01.01.202347122600600
     
05.01.2023471111500500
05.01.202347121750250
05.01.202347112600100
05.01.2023471226000
     
07.01.20234711115000
07.01.2023471211000250
07.01.202347112800200
07.01.2023471226000

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.