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 "produc...
Need Help
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