Extended period chosen?

To solve this in Power BI, you want to dynamically filter your data based on the Visit Date range, showing Calendar Dates and Sales that fall within 5 days before the earliest "Visit Date" (MIN Visit Date) and 5 days after the latest "Visit Date" (MAX Visit Date).

Here's how you can achieve it:

Solution Using DAX
1. Calculated Table Approach
Create a calculated table that dynamically includes the relevant rows.

DAX

FilteredSalesTable =
VAR MinVisitDate = MIN(Sales[visit date]) -- Earliest visit date in the filter context
VAR MaxVisitDate = MAX(Sales[visit date]) -- Latest visit date in the filter context

-- Calculate the desired range
VAR StartDate = MinVisitDate - 5
VAR EndDate = MaxVisitDate + 5

-- Filter the sales table for the relevant calendar dates
RETURN
FILTER(
Sales,
Sales[calendar date] >= StartDate &&
Sales[calendar date] <= EndDate
)

You can now use this table to display the filtered calendar dates and sales data in your visuals.

2. Measure Approach
If you prefer to create a measure instead of a calculated table, you can use this DAX measure:

DAX

FilteredSales =
VAR MinVisitDate = MIN(Sales[visit date]) -- Earliest visit date
VAR MaxVisitDate = MAX(Sales[visit date]) -- Latest visit date

-- Calculate the range
VAR StartDate = MinVisitDate - 5
VAR EndDate = MaxVisitDate + 5

-- Sum sales within the range
RETURN
CALCULATE(
SUM(Sales[sale]),
Sales[calendar date] >= StartDate &&
Sales[calendar date] <= EndDate
)

This measure can be used in a visual where calendar date is on the axis, showing only the relevant sales totals.

3. Additional Dynamic Filter Column (Optional)
You can add a calculated column to identify whether each row falls within the desired range:

DAX

InRange =
VAR MinVisitDate = CALCULATE(MIN(Sales[visit date]), ALL(Sales))
VAR MaxVisitDate = CALCULATE(MAX(Sales[visit date]), ALL(Sales))

-- Calculate the range
VAR StartDate = MinVisitDate - 5
VAR EndDate = MaxVisitDate + 5

-- Check if calendar date is in range
RETURN
IF(
Sales[calendar date] >= StartDate &&
Sales[calendar date] <= EndDate,
1,
0
)

You can then filter your visuals to only include rows where InRange = 1.

Steps to Implement:

1. Add Visit Date Filters: Use slicers or filters for Visit Date to define the range.
2. Use the Calculated Table or Measure: Depending on your choice, add the calculated table or the measure to your visuals.
3. Test Your Filters: Ensure the 5-day adjustment before and after the visit date range works as intended.

Please Mark this as solution if it helps. Appreciate Kudos.

thanks FarhanJeelani,

regarding your "measure"-solution:

thanks FarhanJeelani,

regarding your "measure"-solution:

I think the return script needs more to work? You have only set it up with a "filter"-function? I have tried to wrap it up in a CALCULATION() finction, but it doesn't work:

hi jayjay0306 ,

try like:

1.plot a slicer with a calculated table like:

slicer=VALUES(data[visit date])

2.plot a table visual with calendar dates column and sale column

3.pull a measure like below to the fiter pane of the table visual and choose 1:

measure =

VAR _vdate=SELECTEDVALUE(slicer[visit date])

VAR _date = MAX(data[calendar date])

RETURN

IF(

    _date>=_vdate-5||_date<=_vdate+5,

    1, 0

)

jayjay0306 

New measure:

SalesInExtendedPeriod = 
VAR MinVisitDate = CALCULATE(MIN('YourDataTable'[visit date]), ALLSELECTED('YourDataTable'))
VAR MaxVisitDate = CALCULATE(MAX('YourDataTable'[visit date]), ALLSELECTED('YourDataTable'))

RETURN 
SUMX(
FILTER(
'YourDataTable',
'YourDataTable'[calendar date] >= MinVisitDate - 5 &&
'YourDataTable'[calendar date] <= MaxVisitDate + 5
),
'YourDataTable'[sale]
)

You can use it in visuals directly to get the total sales over the extended dates.

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn