Revenue Comparison Over Custom Dates

[negbc]

Hi, I want to calculate the % Chnage of Revenue from the past 3 months vs the 3 months prior. I need to formula to be dynamic as the year goes on and more data is added.

Below is what I used to calculate the revenue for the last 3 months, but calculting it for 3 months prior is what I'm having issues with.

CALCULATE(

    SUM(Transactions[Revenue]),

        DATESINPERIOD('Date Table'[Date].[Date],

            CALCULATE(

                MAX(Transactions[Invoiced]),All()),

            -3,MONTH

        )

For example, if the dates for the past three months are May 9 to Aug 8th, then I need a formula to have date ranges Feb 9 to May 8th.

Thanks in advance

[Anonymous · Accepted solution]

Hi @negbc 

You can try the following measure

Measure =
VAR a =
    CALCULATE (
        MIN ( 'Date Table'[Date] ),
        DATESINPERIOD (
            'Date Table'[Date],
            CALCULATE ( MAX ( Transactions[Invoiced] ), ALL () ),
            -3,
            MONTH
        )
    )
RETURN
    CALCULATE (
        SUM ( Transactions[Revenue] ),
        DATESINPERIOD ( 'Date Table'[Date], a, -3, MONTH )
    )

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.