Sum by Quarter

[Anonymous]

I currently have a formula that calculates the last 3 months of revenue and it looks like this:

This works as intended but I now need to make some modifications. How can I modify this (or create a new) formula that will:

 Display the last COMPLETED quarter of revenue. This should update as soon as one quarter ends to show the previous quarter's revenue sum multipled by 4. So as of right now, the formula should return all Q1 revenue multipled by 4. Once July hits and Q3 begins, it should be calculating all Q2 revenue multiplied by 4.Thanks!

[v-juanli-msft · Accepted solution]

Hi @Anonymous

“the formula should return all Q1 revenue multipled by 4. Once July hits and Q3 begins, it should be calculating all Q2 revenue multiplied by 4”

Do you mean calculate three months of all quarter for revenue?

I make a test as follows, "measure" uses your formula, and "measure2" uses the following formula.

Measure 2 =
CALCULATE (
    SUM ( Sheet1[value] ),
    FILTER (
        ALL ( Sheet1 ),
        ( Sheet1[date] <= MAX ( [date] ) )
            && MONTH ( Sheet1[date] ) = MONTH ( Sheet1[date] )
    )
)
    * 4

Best Regards

Maggie