Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
seppel123
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)

DateAmount of Cleanings
02.01.202340
03.01.202337
04.01.202368
05.01.202312
06.01.202350

 

2)

DateIndex
04.01.20231
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
Jihwan_Kim
Super User
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.

 

Jihwan_Kim_0-1699942210816.png

 

Jihwan_Kim_1-1699942512167.png

 

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.


Go to My LinkedIn Page


View solution in original post

1 REPLY 1
Jihwan_Kim
Super User
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.

 

Jihwan_Kim_0-1699942210816.png

 

Jihwan_Kim_1-1699942512167.png

 

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.


Go to My LinkedIn Page


Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors