daxpowerbi-desktoppowerpivotpowerbi-custom-visualsdaxstudio

Needs to Display date difference on X-Axis from the selected date slicer in PowerBI

I'm currently working on a Power BI report where I have a line graph representing certain metrics over a period of time. The X-axis of the graph currently displays dates, but I want to change it to display a numeric range centered around a selected date, with zero representing the selected date and negative and positive values representing days before and after, respectively.

Current Setup:

  1. The line graph shows the count values of a certain metric over time. Reference the DAX "Dates to Show"
  2. I'm using a measure called "Dates to Show" to calculate a range of dates based on a selected date from a slicer and the number of days selected by the user. This measure determines the date range for the X-axis.
  3. The Y-axis displays the count values for the metric. This is how now my report looks like: enter image description here

Disconnected Table NumericDateRange enter image description here

Model View: enter image description here

When HolidayName Filter is selected all the 3 line graphs of 3 different years 2023,2022,2021 gets changes as per the slicer value HolidayList[CallDate]. enter image description here

And the number of days to be displayed before and after from the selected date value of HolidayName Filter will be done by this slicer "Select No Of Days to Show" enter image description here

Date to Show Dax enter image description here

Dates to Show = 
VAR _SelectedDate = SELECTEDVALUE(HolidayList[CallDate])
VAR _NumDaysToShow = 'Select No Of Days to Show'[Select No Of Days to Show Value]
VAR _NumericRange =
    GENERATESERIES(-_NumDaysToShow, _NumDaysToShow, 1)
VAR _Dates =
    DATESBETWEEN(
        'Calender Months to Display'[Date],
        _SelectedDate + MINX(_NumericRange, [Value]),
        _SelectedDate + MAXX(_NumericRange, [Value])
    )
VAR _Result =
    CALCULATE(
        [CountMeasure],
        REMOVEFILTERS('HolidayList'),
        KEEPFILTERS(_Dates),
        USERELATIONSHIP('HolidayList'[CallDate], 'Calender Months to Display'[Date])
    )
RETURN
    _Result

Desired Outcome: Instead of displaying specific dates on the X-axis, I want to display a numeric range centered around the selected date. For example, if the selected date is May 29th, 2023, and the user has selected 7 number of days to show from the slicer. Then I want the X-axis to display values like -7, -6, -5, -4, -3, -2, -1, 0, 1, 2, 3, 4, 5, 6, 7 representing the number of days before and after the selected date instead of the dates which is showing now in X-axis.

What I've Tried:

  1. I created a disconnected table called "NumericDateRange" using GENERATESERIES(-1500, 1500, 1) to generate a range of numeric values.
  2. I used this disconnected table in the X-axis of the line graph to display the numeric range.

Issue: While the numeric range appears on the X-axis, the line graph does not display any curves or lines. It seems that the disconnected table is not properly interacting with the data model. The line graph works fine when using dates on the X-axis, but not with the numeric range. I have tried like this as well to change in Dax but now luck

Dates to Show =
VAR _SelectedDate = SELECTEDVALUE(HolidayList[CallDate])
VAR _NumDaysToShow = 'Select No Of Days to Show'[Select No Of Days to Show Value]
VAR _NumericRange =
    GENERATESERIES(-_NumDaysToShow, _NumDaysToShow, 1)
VAR _Dates =
    DATESBETWEEN(
        'Calender Months to Display'[Date],
        _SelectedDate + MINX(_NumericRange, [Value]),
        _SelectedDate + MAXX(_NumericRange, [Value])
    )
VAR _Result =
    CALCULATE(
        [CountMeasure],
        REMOVEFILTERS('HolidayList'),
        KEEPFILTERS(_Dates),
        USERELATIONSHIP('HolidayList'[CallDate], 'Calender Months to Display'[Date])
    )
RETURN
    _Result

I'm quite new to powerbi. Need this help. Thanks for your help and support.

Solution

  • I have created the dax like this show below to achieve the requirement

    NumRangeAroundHolidayByDates = 
VAR _SelectedDate = SELECTEDVALUE(HolidayList[CallDate])
VAR _NumDaysToShow = 'Select No Of Days to Show'[Select No Of Days to Show Value]

-- Filter the DayRange table based on the selected number of days
VAR _DayRangeFiltered =
    FILTER(
        NumericRange,
        NumericRange[NumericRange] >= -_NumDaysToShow && NumericRange[NumericRange] <= _NumDaysToShow
    )

-- Calculate the measure for each day in the filtered range
VAR _Result =
    SUMX(
        _DayRangeFiltered,
        VAR _DayOffset = NumericRange[NumericRange]
        RETURN
            CALCULATE(
                [CountMeasure],
                FILTER(
                    ALL('HolidayList'),
                    DATEDIFF(_SelectedDate, HolidayList[CallDate], DAY) = _DayOffset
                ),
                USERELATIONSHIP('HolidayList'[CallDate], 'Calender Months to Display'[Date])
            )
    )

RETURN
    _Result

    Apart from this I have also created NumericRange table like this. NumericRange = GENERATESERIES(-600, 600, 1)

    enter image description here

    Model View:

    enter image description here

    Now my report looks like this enter image description here

    Additional Information: Apart from the consecutive dates to be replaced by date difference in X-Axis I have also created another report where it works on weekly basis breakdown from the selected Holiday Date

    The respective DAX is as below:

    NumRangeAroundHolidayByWeeks = 
VAR _SelectedDate = SELECTEDVALUE(HolidayList[CallDate])
VAR _NumDaysToShow = 'Select No Of Weeks to Show'[Select No Of Weeks to Show Value]
VAR _Dates = DATESBETWEEN('Calender Months to Display'[Date],_SelectedDate - _NumDaysToShow*7, _SelectedDate + _NumDaysToShow*7)
 var _DatesWith7DayGap = ADDCOLUMNS( 
                                    _Dates,
                                    "RowNum", RANKX(_Dates,[Date],,ASC)

 )

VAR _FilterDates = FILTER(_DatesWith7DayGap, MOD([RowNum],7)=0)
-- Filter the DayRange table based on the selected number of days
VAR _DayRangeFiltered =
    FILTER(
        NumericRange,
        NumericRange[NumericRange] >= -_NumDaysToShow && NumericRange[NumericRange] <= _NumDaysToShow
    )

-- Calculate the measure for each day in the filtered range
VAR _Result =
    SUMX(
        _FilterDates,
        VAR _DayOffset = NumericRange[NumericRange Value]
        RETURN
            CALCULATE(
                [CountMeasure],
                FILTER(
                    ALL('HolidayList'),
                    DATEDIFF(_SelectedDate, HolidayList[CallDate], WEEK) = _DayOffset
                ),
                USERELATIONSHIP('HolidayList'[CallDate], 'Calender Months to Display'[Date])
            )
    )

RETURN
    _Result

    The weekly breakdown report looks like this enter image description here