Bar Chart Filter and X Axis Column Selection

[Settz]

Hi

I have a report that is using a Bar Chart Visualiser to present quantities on Y Axis and Date on the X Axis.

The I need to be able to change the date used on the X Axis between Order Entry Date and Delivery Date - pretty easy so far with a Parameter.

What I am struggling with is that the sales orders to be included in the bar chart fall within a 'Reporting Date Range' that is based on todays date -1 day and todays date + 5 days.

So when the user selects a date option from the Parameter Slicer  need to filter Bar Chart based on the date field selected and the date range for the the selected field.

I have created 2 flag columns in the data source that calculates and presents whether a sales order Order Entry date falls within the Reporting Window and another that indicates if the delivery date falls within the reporting window.

Many Thanks

[v-sshirivolu]

Hi  @Settz ,
Just wanted to check if you had the opportunity to review the suggestion provided?

If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-sshirivolu]

Hi @Settz ,

Just wanted to check if you had the opportunity to review the suggestion provided?

If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-sshirivolu]

Hi @Settz 
Just wanted to check if you had the opportunity to review the suggestion provided?

If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[rajendraongole1 · Accepted solution]

Hi @Settz  -Create a Parameter Table

eg: DateParam =
DATATABLE(
"Date Type", STRING,
{
{"Order Date"},
{"Delivery Date"}
}
)

Now, create a measure that dynamically filters the data based on the selected date type and only includes rows in the reporting window.

ShowQty :=
VAR SelectedDateType = SELECTEDVALUE(DateParam[Date Type])
RETURN
CALCULATE(
SUM(Orders[Qty]),
FILTER(
Orders,
(SelectedDateType = "Order Date" && Orders[In Entry Window] = 1) ||
(SelectedDateType = "Delivery Date" && Orders[In Deliv Window] = 1)
)
)

Create a calculated column or use a measure to switch the date used on the X-axis

SelectedDate :=
SWITCH(
SELECTEDVALUE(DateParam[Date Type]),
"Order Date", Orders[Order Date],
"Delivery Date", Orders[Delivery Date]
)

you will need to create a disconnected calculated table or duplicate your date field and then filter it accordingly in your visual. Hope this works. please check