Comparing volume for selected (slicer) fiscal week to prior week

[Anonymous]

Hi everyone - 

I am trying to create a visual (looking for recommendations) as to which visual to use to compare the selected week's stats (i.e. volume, handle time etc) to the prior week's stats.  I believe the KPI visual will be best, but am definitely open for recommendations, where it has a % change feature as well.  

While the visual recommendation is one thing, the most difficult part and the main ask here is how about doing it.  In the Power BI report, it's designed so that the user can use a slicer to select a particular FISCAL week (it could be the current week or any other week), and it'll show all the metrics that is needed.  

I went down the path of thinking of a KPI visual where I can use this fiscal week's metric as the value and the target as the previous week's.  However, I'm struggling with getting the select fiscal week's PRIOR week's together.  

I have two tables

1) Date Table - fields like Fiscal YPW (such as 20190103 - P representing period), dates, and I've gone as far as creating a new field callsed Fiscal YPW LW (to just have it there so I don't have to create any other DAX functions).  

2) Phones Table - which includes all metrics related our inbound phones.  

The relationship between the two tables is based on date.

I've gone down the path of playing with slicer / visual interactions and turning them on/off, as well as 

• AA_Phone Volume LW = SUMX(FILTER('PHONES',RELATED('FISCAL NEW'[Fiscal YPW LW])=20190102),'PHONES'[Offered Emails]) - too test
• AA_Phone Volume LW = SUMX(FILTER('PHONES',RELATED('FISCAL NEW'[Fiscal YPW LW])=
  SELECTEDVALUE('FISCAL NEW'[Fiscal YPW LW])),'PHONES'[Offered Emails])

I'm pretty sure I'm now going in circles and it's getting nowhere.  Any help will be appreciated from this forum - it's been extremely helpful this far.  Thank you in advance.

[v-yuta-msft]

@Anonymous ,

I've gone down the path of playing with slicer / visual interactions and turning them on/off, as well as 

• AA_Phone Volume LW = SUMX(FILTER('PHONES',RELATED('FISCAL NEW'[Fiscal YPW LW])=20190102),'PHONES'[Offered Emails]) - too test
• AA_Phone Volume LW = SUMX(FILTER('PHONES',RELATED('FISCAL NEW'[Fiscal YPW LW])=
  SELECTEDVALUE('FISCAL NEW'[Fiscal YPW LW])),'PHONES'[Offered Emails])

I'm pretty sure I'm now going in circles and it's getting nowhere.  Any help will be appreciated from this forum - it's been extremely helpful this far.  Thank you in advance.

---

What does measure "AA_Phone Volumn LW" achieve? In addtion, Could you show the "Comparasion" logic and give the expected result?

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]

Hi, @v-yuta-msft - thanks for looking at this!!! 

This is what I'm looking to achieve: 

I want to have a card or a KPI card for each of the metric being reported.  I want to report on THIS week's metric and then also show last week's metric with a % difference.  I probably shouldn't have pasted the DAX in there as it really confuses things - sorry.  

This is what I have in the report view already (both table and graph): 

	Year	Period	Week	Volume	Avg Handle Time (sec)	Avg Answer Time (sec)
20180101	2018	1	1	41295	185	39
20180102	2018	1	2	40352	192	32
20180103	2018	1	3	36452	168	33
20180104	2018	1	4	65485	164	34
20180201	2018	2	1	41555	144	35

These are the two tables: 

Metrics Table
Date	Volume	Handle Time (sec)	Answer Time (sec)
25-Feb-19	5468	956900	191380
26-Feb-19	6562	820200	164040
27-Feb-19	6726	1715038	343008
28-Feb-19	5354	1365270	273054
1-Mar-19	5546	854084	170817
2-Mar-19	5786	948904	189781
3-Mar-19	5854	977618	195524
4-Mar-19	5885	1159345	231869
5-Mar-19	5468	973304	194661
6-Mar-19	5699	1054315	210863
7-Mar-19	6758	1351600	270320
8-Mar-19	5785	1272700	254540
9-Mar-19	5213	1099943	219989
10-Mar-19	5544	831600	166320
11-Mar-19	5956	917224	183445
12-Mar-19	4587	756855	151371
13-Mar-19	5458	731372	146274
14-Mar-19	5456	1391280	278256
15-Mar-19	5745	942180	188436
16-Mar-19	4385	587590	117518
17-Mar-19	4865	802725	160545

Date Table
Date	Fiscal YPW
25-Feb-19	20180101
26-Feb-19	20180101
27-Feb-19	20180101
28-Feb-19	20180101
1-Mar-19	20180101
2-Mar-19	20180101
3-Mar-19	20180101
4-Mar-19	20180102
5-Mar-19	20180102
6-Mar-19	20180102
7-Mar-19	20180102
8-Mar-19	20180102
9-Mar-19	20180102
10-Mar-19	20180102
11-Mar-19	20180103
12-Mar-19	20180103
13-Mar-19	20180103
14-Mar-19	20180103
15-Mar-19	20180103
16-Mar-19	20180103
17-Mar-19	20180103

I've stripped a lot of the other data points from my actual tables to hopefully illustrate what I'm looking to do more accurately.  

Hope this makes more sense now!