Dynamatic calculation based on slicer

[Anonymous]

Hi,

I have been struggling with this question for a while.

Here is what I was trying to accomplish: for a selected day, I will show the stats for that day and average stats for the same day of week for the past one year. For example, if the user pick Nov 14, 2019, the first table present the stats for that single day, the second table shows average stats for thursdays between Nov 14, 2018 and Nov 13, 2019. Currently, I am using two groups of slicers and can only accomplish part of t he task. I block the interaction between slicers under step 1 and the second table, so I can get the average stats for all thursdays, but I could not filter out the selected day and define the range of one year.

thanks,

[v-yuta-msft]

@Anonymous ,

To achieve this, you need to build two slicers. For example, you have slicer1, slicer2, visual1 and visual2. You can disable interaction from slicer1 with slicer2 and visual2, and disable interaction from slicer2 with slicer1 and visual1.

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks, Jimmy.

Could you give me more details regarding how to use two slicers to filter out the current date in the second visual, and dynamatically select one year data to report on? Thanks,

Kathebrine

[v-yuta-msft]

@Anonymous ,

"ynamatically select one year data to report on" means default value in slicer, right? I would suggest you refer to similar thread below:

https://community.powerbi.com/t5/Desktop/Setting-the-Default-Value-of-a-Slicer/td-p/16442

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks for your response, Jimmy.

The post you refered to is interesting, for sure I will be using that in the near future.

But my question is more basic than that. I am struggling with calculating stats for same day of week in the past year giving a date. For example, if Novmeber 14, 2019 is selected, I should present average counts for all the Thursday within one year before Novmeber 14, 2019.  Below is what I got so far, but it doesn't give me exactly what I want. Thanks,

Avg Shift Offered Calls =

CALCULATE(

    AVERAGEX( VALUES( 'SUMMARY'[DT_DMN_ID]), [Call Offered]),

    DATESBETWEEN('Date'[CALENDAR_DATE],

NEXTDAY(DATEADD(LASTDATE('Date'[CALENDAR_DATE]),-1,YEAR)),

        LASTDATE('Date'[CALENDAR_DATE]))

)

Thanks,

Katherine

[v-yuta-msft]

@Anonymous ,

Generally, you need to create a week No. column to mark the week No. and 7 days in a week so that you can achieve the average count in a calculate expression.

In addtion, if you can share some sample data and give the expected result, I can do further analysis.

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@v-yuta-msft 

Could you let me know can I share the file with you? I can create some date in excel and load into Power BI, but how to post the file? I don't see an option to attach a file in the post section. Thanks,

Katherine

[v-yuta-msft]

@Anonymous ,

You can upload the sample file(please do not use the sensitive data) to onedrive and share the onedrive link.

Regards,

Jimmy Tao

[Anonymous]

@v-yuta-msft 

Could you give me hint regarding how to calculate the average? 

Thanks,

Katherine

[Anonymous]

@v-yuta-msft 

Thanks, But my work computer doesn't allow me to install onedrive. I just copy paste the data here. 

Here is the data: Calls is the column I want to summarize. I need the average of all the calls for the same weekday for previous year(here I only post 2 month of data, we could do previous two month).

CALENDAR_DATE	Date_Key	Calls
1/1/2017	20170101	211
1/2/2017	20170102	242
1/3/2017	20170103	254
1/4/2017	20170104	273
1/5/2017	20170105	239
1/6/2017	20170106	194
1/7/2017	20170107	251
1/8/2017	20170108	258
1/9/2017	20170109	247
1/10/2017	20170110	251
1/11/2017	20170111	283
1/12/2017	20170112	243
1/13/2017	20170113	203
1/14/2017	20170114	250
1/15/2017	20170115	254
1/16/2017	20170116	260
1/17/2017	20170117	244
1/18/2017	20170118	274
1/19/2017	20170119	236
1/20/2017	20170120	208
1/21/2017	20170121	265
1/22/2017	20170122	269
1/23/2017	20170123	242
1/24/2017	20170124	253
1/25/2017	20170125	289
1/26/2017	20170126	258
1/27/2017	20170127	237
1/28/2017	20170128	282
1/29/2017	20170129	258
1/30/2017	20170130	264
1/31/2017	20170131	286
2/1/2017	20170201	278
2/2/2017	20170202	246
2/3/2017	20170203	218
2/4/2017	20170204	250
2/5/2017	20170205	257
2/6/2017	20170206	251
2/7/2017	20170207	272
2/8/2017	20170208	329
2/9/2017	20170209	292
2/10/2017	20170210	222
2/11/2017	20170211	303
2/12/2017	20170212	272
2/13/2017	20170213	276
2/14/2017	20170214	276
2/15/2017	20170215	292
2/16/2017	20170216	258
2/17/2017	20170217	243
2/18/2017	20170218	237
2/19/2017	20170219	297
2/20/2017	20170220	292
2/21/2017	20170221	271
2/22/2017	20170222	297
2/23/2017	20170223	265
2/24/2017	20170224	182
2/28/2017	20170228	316

Thank you very much!

Katherine

[Anonymous]

Here is the date table:

DAY_OF_WEEK_NAME_ABBR	Date_Key	DAY_OF_WEEK_S1S7	CALENDAR_DATE
Tue	20170101	3	1/1/2017
Wed	20170102	4	1/2/2017
Thu	20170103	5	1/3/2017
Fri	20170104	6	1/4/2017
Sat	20170105	7	1/5/2017
Sun	20170106	1	1/6/2017
Mon	20170107	2	1/7/2017
Tue	20170108	3	1/8/2017
Wed	20170109	4	1/9/2017
Thu	20170110	5	1/10/2017
Fri	20170111	6	1/11/2017
Sat	20170112	7	1/12/2017
Sun	20170113	1	1/13/2017
Mon	20170114	2	1/14/2017
Tue	20170115	3	1/15/2017
Wed	20170116	4	1/16/2017
Thu	20170117	5	1/17/2017
Fri	20170118	6	1/18/2017
Sat	20170119	7	1/19/2017
Sun	20170120	1	1/20/2017
Mon	20170121	2	1/21/2017
Tue	20170122	3	1/22/2017
Wed	20170123	4	1/23/2017
Thu	20170124	5	1/24/2017
Fri	20170125	6	1/25/2017
Sat	20170126	7	1/26/2017
Sun	20170127	1	1/27/2017
Mon	20170128	2	1/28/2017
Tue	20170129	3	1/29/2017
Wed	20170130	4	1/30/2017
Thu	20170131	5	1/31/2017
Fri	20170201	6	2/1/2017
Sat	20170202	7	2/2/2017
Sun	20170203	1	2/3/2017
Mon	20170204	2	2/4/2017
Tue	20170205	3	2/5/2017
Wed	20170206	4	2/6/2017
Thu	20170207	5	2/7/2017
Fri	20170208	6	2/8/2017
Sat	20170209	7	2/9/2017
Sun	20170210	1	2/10/2017
Mon	20170211	2	2/11/2017
Tue	20170212	3	2/12/2017
Wed	20170213	4	2/13/2017
Thu	20170214	5	2/14/2017
Fri	20170215	6	2/15/2017
Sat	20170216	7	2/16/2017
Sun	20170217	1	2/17/2017
Mon	20170218	2	2/18/2017
Tue	20170219	3	2/19/2017
Wed	20170220	4	2/20/2017
Thu	20170221	5	2/21/2017
Fri	20170222	6	2/22/2017
Sat	20170223	7	2/23/2017
Sun	20170224	1	2/24/2017
Mon	20170225	2	2/25/2017
Tue	20170226	3	2/26/2017
Wed	20170227	4	2/27/2017
Thu	20170228	5	2/28/2017