Forum Discussion

mikess1281's avatar
mikess1281
Regular Visitor
1 year ago

Dynamic baseline calculation

I have a dax cacualtion that currenty finds and averages the baseline for events .  I am trying to figure out how to create a dynamcic baseline where I can choose what years are included in the basel...
Poojara_D12's avatar
Poojara_D12
Super User
1 year ago

 

Hi mikess1281 

 

To create a dynamic baseline where you can select specific years or a range of years:

  1. Create a Year Selection Table: Use a table (e.g., Yearselection) with a list of years you want to include. You can use Calender() to create this table.

  2. Use Slicers: Add a slicer to your report for year selection based on the Yearselection table.

  3. Dynamic DAX Measure: Modify your DAX calculation to filter the events by the selected years:

 

DynamicBaseline = 
VAR SelectedYears = VALUES(YearSelection[Year])
RETURN
    AVERAGEX (
        FILTER (
            'EventData', 
            YEAR('EventData'[EventDate]) IN SelectedYears
        ),
        'EventData'[BaselineValue]
    )

 

 

  • Range Selection (Optional): If you want to allow a year range, use two slicers (start year and end year) and adjust the DAX to filter by the selected range.
  • This allows you to dynamically change the baseline based on the years selected in the slicer.

Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
Consider Subscribing my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS