Forum Discussion

negbc's avatar
negbc
Icon for Helper II rankHelper II
3 years ago
Solved

Revenue Comparison Over Custom Dates

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's avatar
    Anonymous
    3 years ago

    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.

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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.