This FISCAL vs last fiscal with customer hierarchy and duplicate dates in raw data

[Anonymous]

Hi,

I’m trying to do a Period by Period comparison (this fiscal vs last fiscal) of the number of Packages and the Turnaround days (weighted). 

Fiscal Year	Fiscal Period	Fiscal Week	Packages (TY)	Avg Turnaround (TY)	Packages (LY)	Avg Turnaround (LY)
2018			15730	4.14	12820	5.82
	1		951	1.52	732	3.51
		1	245	2.77	121	4.73
		2	283	0.87	209	3.81
		3	206	0.83	214	3.11
		4	217	1.63	188	2.84

I want to be able to do it based on my Fiscal Hierarchy (Fiscal Year -> Fiscal Period -> Fiscal Week -> Date).   I’ve read numerous SAMEPERIODLASTYEAR posts as well as some other videos of custom fiscal year; however, I can’t seem to be able to figure out how to go about doing this. 

I believe the trouble I have is the raw data, where I want my visuals to be able to drill down to the lowest level (actual date), yet want to do some grouped comparisons.  I want to be able to drill down to the actual days (business need) and not remove the details at that level (which I’m able to do and shown below).  The raw data is per below where the arrival date of each of the packages are not sequential (i.e. every single day) and some days are recorded twice (a second package in the same day).

RETURNS TABLE

I also have a FISCAL table that I use to help drill down and comparisons.

FISCAL TABLE

I also created measure to determine the Avg Turnaround of each package using the WEIGHTED AVERAGE DAX. 

WEIGHTED AVERAGE

For the this fiscal vs last fiscal (using my hierarchy), I’m having a problem trying to figure out how to go about doing this.  Do I create a new table and summarize the data in my RETURNS table, and then do a bunch of other calculations on top of it?  Or can I do this within the same table?  I’m also going to have new data coming that I am appending to this as well.   I’m not sure exactly how to do this effectively and hence wanted to ask the experts.  Thank you so much for your help! 

[v-juanli-msft]

Hi @Anonymous 

Packages (TY) is based on Fiscal YPW (in FISCAL table)/ YPW (in RETURNS TABLE),

Packages (LY) is based on YPW LY (in RETURNS TABLE),

Instead of the actual date,

Right?

Best Regards

Maggie

[Anonymous]

Yes, that's correct.

[Anonymous]

Just curious if anyone can point me to the most effective way of doing this without creating another table that summarizes the raw data.  Any input will be appreciated!