Calculate Cumulative Sum in between dates (2 queries)

[seppel123]

Hello everyone,

i am trying to create a Measure in Power BI Desktop that does the following:

I have 2 Queries:

1) Cleaning Data with Column [Date] and Column [Amount of cleanings] - here we see how much was cleaned on a day, the Dates have the whole year except Sundays

2) Change of Waste with Column [Date] and an Index Column - here we see on which dates big waste containers were exchanged

I am interested now in calculated how many Cleanings there were inbetween the exchange of a waste container. 

Tables to simplify: 

1)

Date	Amount of Cleanings
02.01.2023	40
03.01.2023	37
04.01.2023	68
05.01.2023	12
06.01.2023	50

2)

Date	Index
04.01.2023	1
06.01.2023	2

The result should be that depending on the Date of Query 2 there should be the cumulate Sum of Cleanings for this year,

in this example: 

04.01.2023 - 145

06.01.2023 - 62

This is want to plot with date of query 2. 

My problem is that i cannot make it work to lookup the cumulative Sum of the date before the actual date. Hence, i get only a cumulative Sum for every date entry of query 2. 

I tried to make it simpler by adding a column with a cumulated sum in my query 1 to simply to simply subtract the cumulate value of the corresponding date with the date before. Also, with this attempt there will be a point where there is no date "before", in this case the date "01.01.2023" shoud be used to lookup the cumulated sum. 

Here is my attempt, but it failed since it did not subtract any values - probably because the PreviousDate Variable is not correct.

Waste Exchange = 
VAR CurrentID = SELECTEDVALUE('Waste Exchange'[ID])
VAR CurrentDate = SELECTEDVALUE('Waste Exchange'[Date])
VAR PreviousDate =
    IF(
        CurrentID = 1,
        DATE(2023, 1, 1),
        CALCULATE(
        MAX('Waste Exchange'[Date]),
        'Waste Exchange'[ID] = CurrentID - 1
        )
    )
RETURN
    IF(
        ISBLANK(CurrentDate),
        BLANK(),
        CALCULATE(
        SUMX(
            FILTER(
            ‘Cleaning Data’,
            ‘Cleaning Data’[Date] = CurrentDate
            ),
        ‘Cleaning Data’[Cumulative Sum]
        )
    ) -
    CALCULATE(
        SUMX(
            FILTER(
            ‘Cleaning Data’,
            ‘Cleaning Data’[Date] = PreviousDate
            ),
        ‘Cleaning Data’[Cumulative Sum]
        )
    )
)

Hope you guys can help me out with this! 

Thanks,

Seb

[Jihwan_Kim · Accepted solution]

Hi, I am not sure how your datamodel looks like, but please check the below picture and the attached pbix file.

It is for creating a new column.

Expected result CC =
VAR _currentdate = Change[Date]
VAR _previousdateindex =
    MAXX (
        FILTER ( Change, Change[Index] < EARLIER ( Change[Index] ) ),
        Change[Index]
    )
VAR _previousdate =
    MAXX ( FILTER ( Change, Change[Index] = _previousdateindex ), Change[Date] )
RETURN
    IF (
        NOT ISBLANK ( _previousdate ),
        SUMX (
            FILTER (
                Cleanings,
                Cleanings[Date] > _previousdate
                    && Cleanings[Date] <= _currentdate
            ),
            Cleanings[Amount of Cleanings]
        ),
        SUMX (
            FILTER ( Cleanings, Cleanings[Date] <= _currentdate ),
            Cleanings[Amount of Cleanings]
        )
    )