Extended period chosen?

[jayjay0306]

Hi people,

I hope you can help me with a challange:

I have a table, which partly looks like this:

now, I want to use the "visit date" to choose a period ("from-to" date filter), but I need to see the "calendar dates" and "sale" by date,  5 days before the "visit date"-period starts (MIN visit Date) and 5 days after the period ends (MAX visit date).
how do I make this DAX (it is possible to make a calculated table/column as well as a measure)?
All help and inspiration is appreciated. Thanks

Br,

JayJay0306

[FarhanJeelani]

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.

[jayjay0306]

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:

please advise. Thanks.

[FreemanZ]

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

)

[Anonymous · Accepted solution]

Hi all,thanks for the quick reply, I'll add more.

Hi @jayjay0306 ,

Please follow these steps:

1.Use the following DAX expression to create a table

Date Table = CALENDAR(MIN('Table'[visit date]),MAX('Table'[visit date]))

2.Use the following DAX expression to create a measure

_Sale = 
VAR _from = MIN('Date Table'[Date]) - 5
VAR _to = MAX('Date Table'[Date]) + 5
VAR _date = SELECTEDVALUE('Table'[calendar date])
RETURN
IF(_date >= _from && _date <= _to , SUM('Table'[sale]))

3.Final output

Best Regards,
Wenbin Zhou

[jayjay0306]

thanks Wenbin Zhou, it works! 🙂

have a nice day!

br,

Jayjay0306

[Kedar_Pande]

@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