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

[sb4pbi]

Hello Folks,

I have a below client requirement on Power BI desktop, for which i have created dummy data attached. dummy dataprovide any help. 

Requirement: 

Choose Order Date from slicer drop down ex 25th Apr 2024. Based on Order Date selected, you need to go 2 weeks back and compare it. 

Current period needs to be defined as 

Current Period Start Date, order date selected - 7 days :  04-18-2024

Current Period End Date,  order date selected - 1 day :  04-24-2024 

Previous Period needs to be defined as 

Previous Period Start Date, order date selected - 14 days :  04-11-2024

Previous Period End Date,  order date selected - 8 day :  04-17-2024 

Now, you need to Create Table/Matrix as below: 

Please feel free to create Calendar date, if it helps.

*Note: Order Date Slicer drop down is requied single date selection

some of contnt is changed to create this post, as its giving html tag error, pls refer to attached image.

Any help, pointers highly appreciated. 

Thank you,

[v-sdhruv]

Hi @sb4pbi ,
Just wanted to check if you had the opportunity to review the solutions provided?
If the response has addressed your query, please accept it as a solution  so other members can easily find it.
Thank You

[v-sdhruv]

Hi @sb4pbi ,
Just wanted to check if you had the opportunity to review the solutions provided?
If the response has addressed your query, please accept it as a solution  so other members can easily find it.
Thank You

[v-sdhruv]

Hi @sb4pbi ,
Just wanted to check if you had the opportunity to review the solutions provided?
Thank you @danextian , @lbendlin and @DataNinja777  for your prompt and detailed response to the query.
If any of the  responses has addressed your query, please accept it as a solution  so other members can easily find it.
Thank You

[danextian · Accepted solution]

Hi @sb4pbi 

Use a column from  a disconnected dates table for the dropdown as using one from the same or related table will show only that are selected from the slicer  - eg., if you select April 30, your visual will show this date only. Create one using DAX or M. Here's a sample DAX calc table.

DisconnectedDates = 
DISTINCT ( 'Data'[Date] )

Take note that there is no relationship between DisconnectedDates and Data in the screenshot below

Create the following measures

Current Week = 
VAR _RefDate =
    MAX ( DisconnectedDates[Date] )
VAR _start = _RefDate - 7
VAR _end = _RefDate - 1
RETURN
    CALCULATE (
        SUM ( Data[Value] ),
        KEEPFILTERS ( Data[Date] >= _start && Data[Date] <= _end )
    )

Previous Week = 
VAR _RefDate =
    MAX ( DisconnectedDates[Date] )
VAR _start = _RefDate - 14
VAR _end = _RefDate - 8
RETURN
    CALCULATE (
        SUM ( Data[Value] ),
        KEEPFILTERS ( Data[Date] >= _start && Data[Date] <= _end )
    )

 Create the following measure that can be used to sort a table with the current dates first then the previous ones.

Current or Prev = 
VAR _number =
    SWITCH (
        TRUE (),
        NOT ( ISBLANK ( [Current Week] ) ), 1,
        NOT ( ISBLANK ( [Previous Week] ) ), 2
    )
RETURN
    IF ( NOT ( ISBLANK ( _number ) ), REPT ( UNICHAR ( 8203 ), _number ) )

Rename this measure to a space once added to the table so the column name doesn't show. Resize it further so the arrow is not visible.

Please see the attached pbix.

[lbendlin]

Define what you mean by "previous"  and then choose if you want to use Visual Calculations (which have a dedicated PREVIOUS DAX function), one of the window functions (likely OFFSET(-1)), or a more complex DAX statement finding the penultimate value.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

[DataNinja777]

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,