Hi Power BI Community,
I need assistance with adjusting my DAX formulas to dynamically handle date ranges for both budget and actual revenue data.
1) I have two values Budget Revenue and Actual Revenue.
2) Budget Revenue is available from March 2023 to March 2025.
3) Actual Revenue is available from March 2023 to Jun 2024.
Current DAX Formulas
1) Last 3m actual_Revenue =
VAR referenceDate =
MAX('Calendar_table'[Date])
VAR previousDate =
DATESINPERIOD('Refrence Table'[Date], referenceDate, -3, MONTH)
VAR Result =
CALCULATE(
[Actual_Revenue],
REMOVEFILTERS('Calendar_table'),
KEEPFILTERS(previousDate),
USERELATIONSHIP('Calendar_table'[Date], 'Refrence Table'[Date])
)
RETURN
Result
2) Last 3m Budget Revenue =
VAR referenceDate =
MAX('Calendar_table'[Date])
VAR previousDate =
DATESINPERIOD('Refrence Table'[Date], referenceDate, -3, MONTH)
VAR Result =
CALCULATE(
[Budget_Revenue],
REMOVEFILTERS('Calendar_table'),
KEEPFILTERS(previousDate),
USERELATIONSHIP('Calendar_table'[Date], 'Refrence Table'[Date])
)
RETURN
Result
Requirement:-
1. When a month is selected in the slicer:
- The formulas should display the last 3 months from the selected date.
2. When no month is selected:
- The formulas should use the maximum available date of the Actual Revenue (e.g., June 2024) and display the last 3 months from that date.
Issues:
The formulas work correctly when a month is selected in the slicer, but they do not adjust dynamically to handle scenarios where actual revenue data is not available for future months.
Help Needed:
I need guidance on how to modify these formulas to meet the above requirements. Specifically, how can I adjust them so that:
- If no date is selected, the calculation considers the maximum date of the actual revenue data.
- If a date is selected, it calculates the last 3 months based on the selected date.
Thank you in advance for your help!
@amitchandak @Greg_Deckler @mickey64 @Ritaf1983 @rajendraongole1 @bhanu_gautam @SamWiseOwl @Ashish_Mathur @NaveenGandhi @Joe_Barry
