Forum Discussion

RenateBK's avatar
RenateBK
Helper II
3 years ago
Solved

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 dat...
Need Help
  • BA_Pete's avatar
    BA_Pete
    3 years ago

    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

Smalfly's avatar
Smalfly
Responsive Resident
3 years ago

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.

RenateBK's avatar
RenateBK
Helper II
3 years ago

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. 

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."