Forum Discussion

adsam's avatar
adsam
Frequent Visitor
2 years ago
Solved

Cumulative total with condition

Hi, 

 

I m trying to get cumulative total of "Forecast Gross" once "Closing stock" end e.g. Last Closing stock date is 18/03/24 so Forecast should start cumulating from 18/03, I was able to calculate demand after last closing stock date but it is not cumulating. below is the measure. 

Demand Cumulative:=VAR LastClosingStk = CALCULATE(MAX([Transaction Date]),'Date'[DateWithSales] = TRUE,ALL('Date'))
			VAR Result = CALCULATE([Forecast Gross],FILTER('Date','Date'[Date]>=LastClosingStk))
			Return Result

https://1drv.ms/x/s!Agco7LD2yJqrpXn-v3X8sw9g36vt 

Cumulative Total
  • Daoud_H's avatar
    Daoud_H
    2 years ago

    Hi adsam,

     

    Try to update your measure like this : 

    Demand Cumulative:=
VAR LastClosingStk = CALCULATE(MAX([Transaction Date]),'Date'[DateWithSales] = TRUE,ALL('Date'))
VAR CurrentDate = MAX('Date'[Date])
RETURN
    CALCULATE(
        [Forecast Gross],
        FILTER(
            ALL('Date'),
            'Date'[Date] <= CurrentDate && 'Date'[Date] >= LastClosingStk
        )
    )

     

     

2 Replies

  • Daoud_H's avatar
    Daoud_H
    Helper I
    2 years ago

    Hi adsam,

     

    Try to update your measure like this : 

    Demand Cumulative:=
VAR LastClosingStk = CALCULATE(MAX([Transaction Date]),'Date'[DateWithSales] = TRUE,ALL('Date'))
VAR CurrentDate = MAX('Date'[Date])
RETURN
    CALCULATE(
        [Forecast Gross],
        FILTER(
            ALL('Date'),
            'Date'[Date] <= CurrentDate && 'Date'[Date] >= LastClosingStk
        )
    )