cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

## Calculate Cumulative Sum in between dates (2 queries)

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

1 ACCEPTED SOLUTION
Super User

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]
)
)
``````

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Super User

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]
)
)
``````

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors