cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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.

Visit my LinkedIn page by clicking here.

Schedule a meeting with me to discuss further by clicking here.

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.

Visit my LinkedIn page by clicking here.

Schedule a meeting with me to discuss further by clicking here.

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Monthly Update - August 2024

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

Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors