Current vs Previous Period based on date selection in drop down, populate Table or Matrix Power BI

Hi sb4pbi ,

To achieve the dynamic period-over-period comparison in Power BI based on a single date selection, you can follow a robust method using DAX. This approach centers on creating a disconnected table to handle the date selection from a slicer, which prevents it from directly filtering your data model and allows measures to perform custom date calculations.

First, you will need to load your data into Power BI. You can use the "Enter Data" feature to create a table named Sales with your Order Name, Order Date, and Order Amount columns. It's important to ensure the Order Date column is set to a Date data type and Order Amount is a numeric type. After loading the data, it is a best practice to create a dedicated calendar table for time intelligence functions. You can create this table using a DAX expression. Navigate to the Modeling tab and select "New Table", then enter the following formula to generate a table with a continuous range of dates covering all your orders. After creating it, go to the Model view to establish a one-to-many relationship between the Calendar table's Date column and the Sales table's Order Date column.

Calendar =
CALENDAR ( MIN ( 'Sales'[Order Date] ), MAX ( 'Sales'[Order Date] ) )

The key to this solution is a separate, disconnected table for the slicer. This table will hold the unique dates from your orders and will be used to capture the user's selection without affecting the rest of the data model. To create this, again select "New Table" from the Modeling tab and use the following DAX formula. It is critical that you go to the Model view and confirm that this new SlicerDate table has no relationships with any other tables in your model.

SlicerDate = DISTINCT('Sales'[Order Date])

With the data model prepared, you can now write the DAX measures that will perform the dynamic calculations. First, create a measure to capture the value selected in the slicer. This measure will return the single date the user chooses.

Selected Date = SELECTEDVALUE('SlicerDate'[Order Date])

Next, you will create the measure to calculate the sales amount for the "Current Period". This measure uses the Selected Date measure as a variable. It defines the start and end dates for the current period by subtracting 7 days and 1 day, respectively. It then uses the CALCULATE function to sum the Order Amount while applying a filter that only includes dates within this dynamically defined range.

Current Period Amount =
VAR SelectedDate = [Selected Date]
VAR CurrentStartDate = SelectedDate - 7
VAR CurrentEndDate = SelectedDate - 1
RETURN
    CALCULATE (
        SUM ( 'Sales'[Order Amount] ),
        FILTER (
            ALL ( 'Sales' ),
            'Sales'[Order Date] >= CurrentStartDate
                && 'Sales'[Order Date] <= CurrentEndDate
        )
    )

Similarly, you will create another measure for the "Previous Period". The logic is identical, but the date offsets are changed to define the period from 14 days before the selected date to 8 days before it. This creates the one-week period immediately preceding your defined "Current Period".

Previous Period Amount =
VAR SelectedDate = [Selected Date]
VAR PreviousStartDate = SelectedDate - 14
VAR PreviousEndDate = SelectedDate - 8
RETURN
CALCULATE(
    SUM('Sales'[Order Amount]),
    FILTER(
        ALL('Sales'),
        'Sales'[Order Date] >= PreviousStartDate && 'Sales'[Order Date] <= PreviousEndDate
    )
)

Finally, you can build the visuals on your report canvas. Add a slicer visual and use the Order Date field from your disconnected SlicerDate table. In the slicer's formatting options, change the style to "Dropdown" and enable the "Single select" option. Then, add a matrix visual to the report. Drag the Order Name field from your Sales table into the "Rows" well of the matrix. Lastly, drag your two new measures, Current Period Amount and Previous Period Amount, into the "Values" well. The result will be a matrix that dynamically displays the requested comparison, updating instantly whenever a new date is chosen from the dropdown slicer.

Best regards,