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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
RenateBK
Helper I
Helper I

Need help: Cumulative sum function only returns total

Hello, 

 

Edit: Along with the solution I also identified another mistake I made. There was a mismatch between the formatting of my date table and the date column in my fact table (date/time vs date). This cause the relationship between my tables to be crossfiltered instead of being a single one to many -> which in turned made the functions not work. 

 

I have browsed several posts about how to set up a cumulative sum function in the hopes of managing to create one myself, however it does not provide me with the correct output and I am struggling with understanding where I am going wrong. 

 

This is my current formula where OversiktKurs is my Table and [Deltagere Kurs] is the column I would like to calculate the cumulative sum for. I have created a separate date table (dDate), where [Date] has every single date from the start till the end of my fact table. I have also created a relationship between my date table and fact table. 

 

Cumulative sum =
    CALCULATE(
        SUM(OversiktKurs[Deltagere kurs]),
        (dDato[Date]) <= MAX(dDato[Date])
        )
 
I have also tried to include the FILTER function, but that has no impact on the output. 
The output gives me the total sum of the column instead of the cumulative. I would assume there is something I'm doing wrong with the date (I have tried to also use the date column in my OversiktKurs column, but this provides me with the same output).

RenateBK_1-1670228377758.png

 

Thank you in advance for your help, 

Renate

1 ACCEPTED SOLUTION
BA_Pete
Super User
Super User

Hi Renate,

 

Try this as a measure instead, making sure to use dDato fields in visuals, not fact table dates:

_cumulativeSum =
VAR __cDate = MAX(dDato[Date])
RETURN
CALCULATE(
    SUM(OversiktKurs[Deltagere kurs]),
    FILTER(
        ALLSELECTED(dDato),
        dDato[Date] <= __cDate
    )
)

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

8 REPLIES 8
BA_Pete
Super User
Super User

Hi Renate,

 

Try this as a measure instead, making sure to use dDato fields in visuals, not fact table dates:

_cumulativeSum =
VAR __cDate = MAX(dDato[Date])
RETURN
CALCULATE(
    SUM(OversiktKurs[Deltagere kurs]),
    FILTER(
        ALLSELECTED(dDato),
        dDato[Date] <= __cDate
    )
)

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Hello Pete, 

 

Thank you for the formula!

It seems I may have created a circular dependency as my Date table is created and based on the dates in my fact table; 

        CALENDAR(DATE(YEAR(MIN(OversiktKurs[Dato])),1,1),
        DATE(YEAR(MAX(OversiktKurs[Dato])),12,31)),
 
I'm not sure if this is ok to do, but when I swap out the formula with the date column in my fact table to test it out, it returns the total sum again. 
_cumulativeSum =
VAR __cDate = MAX(OversiktKurs[Dato])
RETURN
CALCULATE(
    SUM(OversiktKurs[Deltagere kurs]),
    FILTER(
        ALLSELECTED(OversiktKurs),
        OversiktKurs[Dato] <= __cDate))
 
 

Hi Renate,

 

There's no need to swap out anything in the measure I gave you. It should work exactly as it is.

Please try the measure exactly as I wrote it and let me know how you get on.

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




I am receiving a circular dependency error - I fear it may be due to how I created my date table. 

RenateBK_1-1670234643843.png

 

 

 

Are you trying to create this as a calculated column rather than a measure?

You shouln't get a circular dependency when creating a measure as your dDato table would not get recalculated at thatpoint.

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




You are correct, it completely slipped my mind.

It worked now, thank you so much for your help. 

 

Best regards, 

Renate

Smalfly
Resolver III
Resolver III

Hi @RenateBK ,

 

I always use earlier to calculate a cumulative sum in combination with a date value. Earlier is more flexible than the max function you are currently using. Please check this for an example on calculating a cumulative sum using the earlier function.

Thank you for the response, I tried to use the EARLIER function, but I am receiving an error saying that "the syntax for date is wrong" and the error message ending with 6 parenteses instead of 3. 

RenateBK_0-1670232995958.png

If I try to use it without .Date

Cumulative sum =
    CALCULATE(
        SUM(OversiktKurs[Deltagere kurs]),
        FILTER(OversiktKurs,dDato[Date] <= EARLIER(dDato[Date]))
        )

 

I get the error "A single value for column 'Tangoe Owner Email' in table 'Monthly Billing' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors