Question

What is the best way to handle a scenario where multiple date columns exist in a fact table?
I have a fact table that contains multiple date columns, such as Order Date, Ship Date, and Delivery Date. I need a Power BI model that allows users to analyze data based on different date contexts dynamically. What is the best approach for handling this in DAX and Power BI? Should I create multiple date tables or use a single date table with role-playing relationships?

Answer 1

Create a disconnected table (Time Periods) with values: "Current Year," "Last Year," etc. Use a DAX measure with SWITCH() and SELECTEDVALUE() to apply filters dynamically:

SalesFiltered =
VAR SelectedPeriod = SELECTEDVALUE('Time Periods'[Period Name])
RETURN
SWITCH(
    SelectedPeriod,
    "Current Year", CALCULATE([Total Sales], YEAR('Sales'[Order Date]) = YEAR(TODAY())),
    "Last Year", CALCULATE([Total Sales], YEAR('Sales'[Order Date]) = YEAR(TODAY()) - 1),
    "Last 3 Years", CALCULATE([Total Sales], 'Sales'[Order Date] >= DATE(YEAR(TODAY()) - 3, 1, 1)),
    [Total Sales]  -- Default case
)

Add the slicer to your report and use SalesFiltered in visuals. The measure will update dynamically!