Revenue Comparison Over Custom Dates

negbc — Tue, 08 Aug 2023 18:36:18 GMT

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

Re: Revenue Comparison Over Custom Dates

Anonymous — Thu, 10 Aug 2023 01:52:26 GMT

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.

topic Revenue Comparison Over Custom Dates in DAX Commands and Tips

Revenue Comparison Over Custom Dates

Re: Revenue Comparison Over Custom Dates