Forum Discussion
Calculate Previous Months Aggregate Amount
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
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.
2 Replies
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 ) )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.
