How to get cumulative totals until date in column matrix

[TK12345]

Hi there, 

I have a question about a DAX-Measure I would like to make.

What I would like to make is a Matrix, that shows me the amount of stock, untill the date in the LastDate column. So I would like to make a matrix with _KeyArtikel in the rows and LastDate in columns. The value I would like to calculate is: what is the amount of stock I have for the date in LastDate column. So the columns will be 13-07-2023, 13-08-2023, 13-05-2023 and 13-06-2023. For the first column (13-07-2023) I would like the sum of amount for all the dates from start of my calendar to 13-07-2023. So that will be 32.223. My Key_date 2306060 corespondates with 06-06-2023 in my calendar table. Next column (13-08-2023) needs to be sum of amount where my min date is first date in calendar and max date is 13-08-2023 so that will be 62.223. 

I have tried using DatesBetween(date, startdate, enddate) Where startdate is first date of calendar and enddate is max LastDate. But it still does not work. 

Who could help me out?

Expected outcome: 

Artikel	13-7-2023	13-8-2023	13-5-2024	13-6-2024
1a	32223	60223	162351	162351

I have the following test-data:

_KeyArtikel	_KeyDatum	Verwijzing	LastDate	Amount	VerwijzingCategorie
1a	20230606	StartStock		876	StartStock
1a	20230606	StartStock		876	StartStock
1a	20230606	StartStock		7515	StartStock
1a	20230606	StartStock		9450	StartStock
1a	20230606	StartStock		1530	StartStock
1a	20230606	StartStock		7740	StartStock
1a	20230606	Line 1	13-7-2023	2118	Production
1a	20230607	Line 1	13-7-2023	2118	Production
1a	20230607	Line 1	13-8-2023	2500	Production
1a	20230608	Line 1	13-8-2023	2500	Production
1a	20230609	Line 1	13-8-2023	2500	Production
1a	20230610	Line 2	13-8-2023	2000	Production
1a	20230610	Line 1	13-8-2023	2500	Production
1a	20230611	Line 2	13-8-2023	2000	Production
1a	20230612	Line 2	13-8-2023	2000	Production
1a	20230613	Line 2	13-8-2023	2000	Production
1a	20230614	Line 2	13-8-2023	2000	Production
1a	20230617	Line 2	13-8-2023	2000	Production
1a	20230618	Line 2	13-8-2023	2000	Production
1a	20230619	Line 2	13-8-2023	2000	Production
1a	20230620	Line 2	13-8-2023	2000	Production
1a	20230621	Line 2	13-8-2023	2000	Production
1a	20240314	Line 2	13-5-2024	9904	Production
1a	20240314	Line 2	13-5-2024	16223	Production
1a	20240314	Line 2	13-5-2024	13896	Production
1a	20240314	Line 2	13-5-2024	13548	Production
1a	20240314	Line 2	13-5-2024	14075	Production
1a	20240319	Line 2	13-5-2024	11857	Production
1a	20240326	Line 2	13-5-2024	11902	Production
1a	20240402	Line 2	13-6-2024	8723	Production

[v-rzhou-msft · Accepted solution]

Hi @TK12345 ,

I suggest you to try code as below to create a measure.

OutCome = 
IF (
    ISBLANK ( MAX ( 'Table'[LastDate] ) ),
    BLANK (),
    CALCULATE (
        SUM ( 'Table'[Amount] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[_KeyArtikel] ),
            'Table'[LastDate] <= MAX ( 'Table'[LastDate] )
        )
    )
)

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-rzhou-msft · Accepted solution]

Hi @TK12345 ,

I suggest you to try code as below to create a measure.

OutCome = 
IF (
    ISBLANK ( MAX ( 'Table'[LastDate] ) ),
    BLANK (),
    CALCULATE (
        SUM ( 'Table'[Amount] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[_KeyArtikel] ),
            'Table'[LastDate] <= MAX ( 'Table'[LastDate] )
        )
    )
)

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[TK12345]

Hi Rico

Thanks for your reply. I can get the incomming goods right now. I just forgot the most difficult part. Maybe you know how to do it. In the same table I also have "Sales" (where the LastDate is empty). I do have the sales date in the key. 

What I would like to show in the visual is what is the amount of Sales until the first Max date. So my max date is 13-07-2023 and 13-08-2023 and 13-05-2024. What I would like to see with the sales is how many sales did I have before or on 13-07-2023. In this case it will be -600. How much untill or after 13-08-2023 it will be -600 as well and before or after 13-05-2024 - 1050. I would like to plot that on the same column as the the LastDate in the Matrix. How do I get that. 

When I do Incoming - Outgoing the total is right. But it does not plot the sales on the column of the last date. 

See data: 

1a	20240314	Line 2	13-5-2024	13896	Production
1a	20240314	Line 2	13-5-2024	13548	Production
1a	20240314	Line 2	13-5-2024	14075	Production
1a	20240319	Line 2	13-5-2024	11857	Production
1a	20240326	Line 2	13-5-2024	11902	Production
1a	20240402	Line 2	13-6-2024	8723	Production
1a	20230608	Sales 1		-100	Sales
1a	20230609	Sales 1		-100	Sales
1a	20230610	Sales 1		-100	Sales
1a	20230610	Sales 1		-100	Sales
1a	20230611	Sales 1		-100	Sales
1a	20230612	Sales 1		-100	Sales
1a	20240314	Sales 1		-150	Sales
1a	20240314	Sales 1		-150	Sales
1a	20240314	Sales 1		-150	Sales

Just added 9 sales rows to the data

[TK12345 · Accepted solution]

Got it:

CALCULATE(

    SUM(amount),

    'mdw Fact'[OntvangstOfUitgifte] = "Ontvangst",

    'mdw Fact'[Keydatum] <= SELECTEDVALUE('mdw Fact'[LastDate]),

    ALL('mdw FactBehoeftePlanRegel'[VervaldatumUlc])

)


Thanks for the help