Graph of accumulated values with differentiation of categories

[Till__]

Dear all, 

I want to achieve stacked bar graph over time. The graph should show the total value at each date which is the accumalted value while the stacked bar shows of which categories the total value consists of at the date.

That is: 

At the 10.10.2022 I have a bar with a value of 6 in category 1. 

At the 23.10.2022 I have the next bar with a value of 56 which is 100% catgeory 1 

At the 24.12.2022 I have the next bar with a value of 58 where 6 are category 1 (from Product Fanta) and 52 are now category 2 (from product Coca Cola). 

At the 01.01.2023 everything is in category 2 again

At the 30.01.2023 I have an accumulated value of 60  where 8 are category 1 (from Product Fanta) and 52 are now category 2 (from product Coca Cola). 

The table with the consolidated informations: 

Product	Category	Date of Change	Value	Total value	Change in total
Fanta	Category 1	10.10.2022	6	6
Coca Cola	Category 1	23.10.2022	50	56
Coca Cola	Category 2	24.12.2022	52	58	2
Fanta	Category 2	01.01.2023	7	59	1
Fanta	Category 3	30.01.2023	8	60	1

For me the difficutly is to show the change of a category over time while the other product is still in another category.

Thank you in advance!

Best

Till 

[Anonymous]

Hi @Till__ ,

I created a sample pbix file(see the attachment), please check if that is what you want. You can create two measures as below to get it:

Total value = 
VAR _date =
    SELECTEDVALUE ( 'Table'[Date of Change] )
VAR _category =
    SELECTEDVALUE ( 'Table'[Category] )
VAR _product =
    SELECTEDVALUE ( 'Table'[Product] )
VAR _predate =
    CALCULATE (
        MAX ( 'Table'[Date of Change] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Product] <> _product
                && 'Table'[Date of Change] < _date
        )
    )
VAR _pvalue =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Date of Change] = _predate )
    )
RETURN
    _pvalue + SUM ( 'Table'[Value] )

Change in total = 
VAR _date =
    SELECTEDVALUE ( 'Table'[Date of Change] )
VAR _category =
    SELECTEDVALUE ( 'Table'[Category] )
VAR _product =
    SELECTEDVALUE ( 'Table'[Product] )
VAR _predate =
    CALCULATE (
        MAX ( 'Table'[Date of Change] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Product] <> _product
                && 'Table'[Category] <> _category
                && 'Table'[Date of Change] < _date
        )
    )
VAR _prevalue =
    MAXX (
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Date of Change] < _date ),
        [Total value]
    )
RETURN
    IF ( ISBLANK ( _predate ), BLANK (), [Total value] - _prevalue )

If the above one can't help you, please provide more raw data in your table (exclude sensitive data) with Text format, your visual Fields settings and your expected result with backend logic and special examples? It would be helpful to find out the solution. You can refer the following links to share the required info:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[Till__]

Dear @Anonymous , 

Thank you so much for your answer. I want to achieve the distinction of categories at different dates. 

Below is a Screenshot of a graph with some further explanation. The difficulty is to distinguish the sum of a value at one date into two or more categories if only one Product changes and not the total sum. As below at the deccember 24th 2022 we have 6 in category one from the Product Fanta but 52 in Category 2 from the Product Coca Cola. 

Thank you for your respone! I really appreciate it. 

Best 
Till