Calculate Previous Months Aggregate Amount

[Anonymous]

Hi,  

I need to add all the amounts of the orders in previous months to the first week of the selected month (in the slicer).

Thanks in advance 

[v-frfei-msft · Accepted solution]

Hi @Anonymous ,

I have created sample for your reference.

Measure = var sle = MIN('Table'[Date])
var pre = CALCULATE(SUM('Table'[value]),FILTER(ALL('Table'),'Table'[Date]< sle))
return
pre+ CALCULATE(SUM('Table'[value]),FILTER('Table','Table'[week] = MIN('Table'[week])))

Pbix as attached.

[danextian]

Hi @Anonymous ,

It is always a good practice to post a sample data to get a better and faster response.

The formula below is just based on your description and it assumes that the first week ends at the seventh of the month and there is not separate dates table.

Running Count =
VAR FirstWeek =
    EOMONTH ( MAX ( Table[Date] ), -1 ) + 7
RETURN
    CALCULATE ( [Amount], FILTER ( ALL ( Table[Date] ), Table[Date] <= FirstWeek ) )