Forum Discussion

thelu's avatar
thelu
New Member
2 years ago

Problem with cumulative values

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

Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    2 years ago

    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's avatar
      thelu
      New Member
      2 years ago


      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