3 charts showing next 3 weeks of data thru dropdown filter

[Serrano]

Hello,

I need to make a page where a user selects a week in table SENSORD by means of the dropdown slicer. The page has 3 charts with X the dates in table SENSORD (including week number), and Y the values of a sensor.

Based on the selected week, I need to show in 1st chart the Y values of the selected week (which is easy). But in 2nd chart I want to show the values of the next week, and in the 3rd chart the one of the following week. All with one slicer (to make it user-friendly). Any idea how?

Thank you.

[v-nmadadi-msft]

Hi @Serrano 

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the community members for the issue worked. If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Thanks and regards

[v-nmadadi-msft]

Hi @Serrano 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If our responses has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-nmadadi-msft]

Hi @Serrano ,

May I ask if you have resolved this issue using suggestions provided by @bhanu_gautam ? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[bhanu_gautam]

@Serrano 

Ensure you have a date table that includes all the dates and their corresponding week numbers. This table will be used to create relationships and slicers.

Establish relationships between your date table and the SENSORD table based on the date field.

Create a measure for the selected week.
Create a measure for the next week.
Create a measure for the week after the next week.

DAX
SelectedWeekValues =
CALCULATE(
SUM(SENSORD[SensorValue]),
FILTER(
SENSORD,
SENSORD[WeekNumber] = SELECTEDVALUE(DateTable[WeekNumber])
)
)

NextWeekValues =
CALCULATE(
SUM(SENSORD[SensorValue]),
FILTER(
SENSORD,
SENSORD[WeekNumber] = SELECTEDVALUE(DateTable[WeekNumber]) + 1
)
)

FollowingWeekValues =
CALCULATE(
SUM(SENSORD[SensorValue]),
FILTER(
SENSORD,
SENSORD[WeekNumber] = SELECTEDVALUE(DateTable[WeekNumber]) + 2
)
)

Create three separate charts.
Assign the SelectedWeekValues measure to the first chart.
Assign the NextWeekValues measure to the second chart.
Assign the FollowingWeekValues measure to the third chart.

[Serrano]

@bhanu_gautam 

Hi. Your solution works for the 1st chart, but not for the 2nd and 3rd.

I don't know if it is not working because SENSORD is already filter.

According to the command ISFILTERED, it shows negative.

Even without using SELECTEDVALUE:

SENSORD[WeekNumber] = 6

still not working for 2nd and 3rd.

Any idea why?

[bhanu_gautam]

If the measures for the next week and the week after that are not working, it might be due to the filtering context. Ensure that the SENSORD table is not being filtered in a way that conflicts with the measures. You can also try using the ALL function to remove any existing filters on the SENSORD table:

NextWeekValues =
CALCULATE(
SUM(SENSORD[SensorValue]),
FILTER(
ALL(SENSORD),
SENSORD[WeekNumber] = SELECTEDVALUE(DateTable[WeekNumber]) + 1
)
)

FollowingWeekValues =
CALCULATE(
SUM(SENSORD[SensorValue]),
FILTER(
ALL(SENSORD),
SENSORD[WeekNumber] = SELECTEDVALUE(DateTable[WeekNumber]) + 2
)
)

[Serrano]

@bhanu_gautam 
Hi.

You solution works fine but only by making 2 additional copies of SENSORD to calculate NextWeekValues and FollowingWeekValues. And the 2 copies not linked to SENSORD nor DateTable.

Thank you, your insigth was usefull.