Revenue Formula

[Anonymous]

I have this table:

I didn't post revenue amounts for security reasons, but the column is just called "revenue" and there is an amount associated with each invoice date. What I'm trying to figure out is how to calculate the sum of revenue for each account (co) for the last 3 months, then taking that sum and multiplying it by 4. The tricky part is keeping it dynamic. New invoice dates populate each month, so as time progresses it will always need to calculate the most previous 3 months of revenue. So for instance, May is almost over, so once June comes it would need to take the sum of revenue for March, April and May for each account and multiply that sum by 4 to get an annual revenue amount for each account. Is this possible?

[v-yuta-msft · Accepted solution]

Hi mrainey,

It's a classic running total question. Create a measure and use DAX formula like pattern below:

Result =
CALCULATE (
    SUM ( table[value] ),
    ALLEXCEPT ( table, table[Co] ),
    FILTER (
        table,
        table[Invoice Date]
            >= MAX ( table[Invoice Date] ) - 4
            && table[Invoice Date]
                <= MAX ( table[Invoice Date] - 1 )
    )
)
    * 4

Regards,

Jimmy Tao

[kcantor]

@AnonymousI would recommend just creating th calculation without the date constraints. When you put it on the report page, load your datekey as a visual, page, or report level filter and use the Relative Date filter function to set it for "In the last 3 months" which will cause the calculation to be dynamic.