topic Re: Extended period chosen? in DAX Commands and Tips

Extended period chosen?

jayjay0306 — Tue, 26 Nov 2024 10:10:04 GMT

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

Re: Extended period chosen?

FarhanJeelani — Tue, 26 Nov 2024 10:16:06 GMT

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:

Add Visit Date Filters: Use slicers or filters for Visit Date to define the range.
Use the Calculated Table or Measure: Depending on your choice, add the calculated table or the measure to your visuals.
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.

Re: Extended period chosen?

jayjay0306 — Tue, 26 Nov 2024 11:58:48 GMT

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.

Re: Extended period chosen?

FreemanZ — Tue, 26 Nov 2024 14:21:42 GMT

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

)

Re: Extended period chosen?

Anonymous — Wed, 27 Nov 2024 02:17:50 GMT

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

Re: Extended period chosen?

Kedar_Pande — Wed, 27 Nov 2024 05:59:09 GMT

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

Re: Extended period chosen?

jayjay0306 — Wed, 27 Nov 2024 10:38:02 GMT

thanks Wenbin Zhou, it works! 🙂

have a nice day!

br,

Jayjay0306