Problem with cumulative values

[thelu]

Good morning,

I have a Power BI project where I have a measure "products[total_value]" and a date column called "products[registration_date]". I need to create a table that shows each day of "products[registration_date]" (with the time range chosen via filter) along with the corresponding value of "products[total_value]" and the cumulative value of this measure.

The result should look like this:

products[registration_date] | products[total_value] | CUMULATIVE
---------------------------------------------------------------------
21/06/2021                         |  0.00                           | 0.00
22/06/2021                         | -2720.00                     | -2720.00
23/06/2021                         | -31020.00                   | -33740.00
24/06/2021                         | -1824.45                     | -35564.45

I tried different measures like:

Comulative_1 =
SUMX(
ADDCOLUMNS(
CALENDAR(MIN(products[registration_date]), MAX(products[registration_date])),
"Date2",
[Date]
),
CALCULATE(
products[total_value],
FILTER(
ALL(products),
products[registration_date] <= [Date]
)
)
)

This measurement starts off well with the calculations but as it progresses through the days it adds slightly different values to the cumulative compared to the value of products[total_value], therefore it alters the cumulative

Same thing for this measurement:

Comulative_2 = 
VAR CurrentDate = MAX(products[registration_date])
VAR MinDate = CALCULATE(MIN(products[registration_date]), ALL(products))
VAR CumulativeValue =
    IF(
        CurrentDate >= MinDate,
        SUMX(
            FILTER(
                ALL(products),
                products[registration_date] <= CurrentDate
            ),
            products[total_value]
        )
    )
VAR PreviousDate = CurrentDate - 1
VAR PreviousCumulativeValue =
    IF(
        PreviousDate >= MinDate,
        SUMX(
            FILTER(
                ALL(products),
                products[registration_date] = PreviousDate
            ),
            products[total_value]
        )
    )
RETURN
    IF(
        CurrentDate > MinDate,
        CumulativeValue + PreviousCumulativeValue,
        CumulativeValue
    )

With the above measurement I get a result like this (small example time interval):

products[registration_date] | products[total_value] | COMULATIVE
---------------------------------------------------------------------
22/06/2021                         | 0.00                             | -2720.00
24/06/2021                         | -31020.00                   | -64760.00
30/06/2021                         | -1824.45                     | -79784.45

For example, in the second line, double -31020.00 is inexplicably calculated and -2720.00 subtracted

Thanks so much in advance for your help

Cristian

[amitchandak]

@thelu , with help from date/calendar table

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(all('Date'),'Date'[date] <=max('Date'[date])))

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(date),date[date] <=max(date[Date])))

Cumm Based on Date = CALCULATE([Net], Window(1,ABS,0,REL, ALL('date'[date]),ORDERBY('Date'[date],ASC)))

Cumm Based on Date = CALCULATE([Net], Window(1,ABS,0,REL, ALLSELECTED('date'[date]),ORDERBY('Date'[date],ASC)))

One month behind

Cumm Sales =
var _date = max('Date'[date])
Var _max = date(year(_date)-1, month(_date), day(_date))
return
CALCULATE(SUM(Sales[Sales Amount]),filter(all('Date'),'Date'[date] <= _max))

Running Total/ Cumulative:
https://www.youtube.com/watch?v=h2wsO332LUo&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=42

Continue to explore Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc
https://medium.com/@amitchandak/power-bi-window-function-3d98a5b0e07f

[thelu]

Thanks so much for the reply,

I tried these two formulas:

Cum1= CALCULATE(SUM(products[total_value]),filter(all(products),products[registration_date] <=max(products[registration_date] )))
 
Cum2 = CALCULATE(SUM(products[total_value]),filter(allselected(products),products[registration_date] <=max(products[registration_date] )))
 
But in both cases I get the error:

Column 'total_value' of the table 'products' not found or not used in this expression.

And passing over products[total_value] I see the error "the parameter is not of the correct type".
products[total_value] exists and it is a measure that I can also use in other measures, I don't understand this error...

Thanks