Solved: Calculate Previous Months Aggregate Amount

Anonymous · ‎03-04-2020

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 · ‎03-04-2020

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.

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

v-frfei-msft · ‎03-04-2020

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.

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

danextian · ‎03-04-2020

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 ) )

Dane Belarmino | Microsoft MVP | Proud to be a Super User!

Did I answer your question? Mark my post as a solution!

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.