Dynamatic calculation based on slicer

Question

Hi,&nbsp;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.&nbsp;thanks,&nbsp;&nbsp;

v-yuta-msft · Answer

Anonymous&nbsp;,
&nbsp;
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.
&nbsp;
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 · Answer

Thanks, Jimmy.&nbsp;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,&nbsp;Kathebrine&nbsp;&nbsp;

v-yuta-msft · Answer

Anonymous&nbsp;,
&nbsp;
"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
&nbsp;
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 · Answer

Thanks for your response, Jimmy.&nbsp;The post you refered to is interesting, for sure I will be using that in the near future.&nbsp;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.&nbsp; Below is what I got so far, but it doesn't give me exactly what I want. Thanks,&nbsp;Avg Shift Offered Calls = CALCULATE(&nbsp;&nbsp;&nbsp; AVERAGEX( VALUES( 'SUMMARY'[DT_DMN_ID]), [Call Offered]),&nbsp;&nbsp;&nbsp; DATESBETWEEN('Date'[CALENDAR_DATE], NEXTDAY(DATEADD(LASTDATE('Date'[CALENDAR_DATE]),-1,YEAR)),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; LASTDATE('Date'[CALENDAR_DATE])))&nbsp;Thanks,&nbsp;Katherine

v-yuta-msft · Answer

Anonymous&nbsp;,
&nbsp;
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.
&nbsp;
In addtion, if you can share some sample data and give the expected result, I can do further analysis.
&nbsp;
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.

Forum Discussion

Dynamatic calculation based on slicer

10 Replies

Related content

Calculation not working when slicers applied

Date Slicer not Responsive to Calculated Date Table

MoM calculation

CALCULATE with REMOVEFILTERS still affected by slicer

DAX calculation with slicer values