Forum Discussion

seppel123's avatar
seppel123
Frequent Visitor
2 years ago
Solved

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 ...
  • Jihwan_Kim's avatar
    Jihwan_Kim
    2 years ago

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

     

     

     

Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
2 years ago

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