How to select a dynamic Date Dimension on X-Axis of a Visual based on Date Range in Power BI

How to Select a Date Dimension dynamically on X-Axis for a Visual based on Selected Date Range in Power BI

Scenario:

Suppose we have a Data Model as per below.

The relationships are as per below:

Requirement Criteria:

Case 1:

When user selected a Date Range of <=1 Month(say, 30 Days), then the X-Axis of the Visual should use the Date Dimension.

Case 2:

When user selected a Date Range of <=3 Months(say, 90 Days), then the X-Axis of the Visual should use the Week Dimension.

Case 3:

When user selected a Date Range of <=12 Months( Say , 365 Days), then X-Axis of the Visual should use the Month Dimension.

Case 3:

When user selected a Date Range of <=12 Months( Say , 365 Days), then X-Axis of the Visual should use the Month Dimension.

Case 4:

When user selected a Date Range of <=15 Months then X-Axis of the Visual should use the Quarter Dimension.

Case 5:

When user selected a Date Range of <=36 Months then X-Axis of the Visual should use the Year Dimension.

Note: You can define custom Date Ranges to Chose Dimension as per your requirement.

We can fulfil the above Criteria, as discussed below.

Step1: Create a Dynamic Date Dimension table using Field Parameters:

This Field Parameter table will be using for Dynamic X-Axis for the Visual.

DateDimension = 
    {
        (“Date”, NAMEOF(‘DimCalendar’[DimDate]), 1),
        (“Week”, NAMEOF(‘DimCalendar’[WeekNumYear]), 2),
        (“Month”, NAMEOF(‘DimCalendar’[MonthYear]), 3),
        (“Quarter”, NAMEOF(‘DimCalendar’[Qtr_Yr]), 4),
        (“Year”, NAMEOF(‘DimCalendar’[DimYear]), 5)
    }

Note: I have renamed Output Fields as per below:

Step2: Create a Bridge Table for Date Dimension:

This table will be used as a Bridge between DimCalendar and DateDimension.

DateRangeBridge = 
ADDCOLUMNS(
    CALENDAR(MIN(tbl_OrderDetails[Order_Date]), MAX(tbl_OrderDetails[Order_Date])),
    “DateRange”, 
    VAR vMonthCount = DATEDIFF(MIN(tbl_OrderDetails[Order_Date]),[Date], MONTH)+1
    RETURN
    SWITCH(TRUE(),
        vMonthCount = 1, 1, –Date
        vMonthCount <= 3, 2, –Week
        vMonthCount <= 12, 3, –Month
        vMonthCount <= 15, 4, –Quarter
        vMonthCount <= 36, 5 –Year
        )
    )

Step3: Enable the required relationships between DimCalendar, DateRangeBridge and Date Dimension:
The One to Many relationship between DimCalendar (1:M) DateRangeBridge with Cross Filter direction Single.

The One to Many relationship between DateDimension (1:M) DateRangeBridge with a Cross Filter direction Both.

Step4: Build a Column/Bar Chart with use of DateDimension[DimName] on the X-Axis:

Here I have considered a Date Slicer is based on DimCalendar[DimDate], Year Slicer is based on DimCalendar[DimYear].

The Dynamic Date Dimension Slicer is based on DateDimension[DimName], which is Optional as the Date Diemension is enough to update the X-Axis based on a range of Dates selected.

Results:

Case 1:

When user selected a Date Range of <=1 Month(say, 30 Days), then the X-Axis of the Visual should use the Date Dimension.

Case 2:

When user selected a Date Range of <=3 Months(say, 90 Days), then the X-Axis of the Visual should use the Week Dimension.

Case 3:

When user selected a Date Range of <=12 Months( Say , 365 Days), then X-Axis of the Visual should use the Month Dimension.

Case 4:

When user selected a Date Range of <=15 Months then X-Axis of the Visual should use the Quarter Dimension.

Case 5:

When user selected a Date Range of <=36 Months then X-Axis of the Visual should use the Year Dimension.

——————————————————————————————————–

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

——————————————————————————————————–

Source link

How to select a dynamic Date Dimension on X-Axis of a Visual based on Date Range in Power BI | Power BI Blog

Be the first to comment

Leave a Reply Cancel reply