Working with 4 week time periods in calculations

[jpt1228]

Hello first off I want to thank all the smart people on here for helping. I am having a difficult time importing data and wokring with it. The main problem is the data that I am working with is available every 4 weeks or 13 periods a year. I want to be able to compare 4 weeks VS the same time last year, 12 weeks VS same time last year, 24 and 52 weeks. I can do this with data that is reported by day or calendar month but when a single month has data reported 2x in that single month (see red text for October 2016) I can't figure it out. Thinking I have to index the dates and compare index 2 2016 VS index 2 2017 rather than Month and date.

	Geography	Data
	Region 1			Region 2
Time Description	Sum of Dollars	Sum of Units	Sum of Stores	Sum of Dollars	Sum of Units	Sum of Stores
Quad End 2015-Dec-27	5430.37	919.86	33.053	56315.013	10225.176	42.59
Quad End 2015-Nov-01	5808.845	1017.751	31.351	56575.395	10181.962	41.194
Quad End 2015-Nov-29	6184.462	1055.398	33.516	59102.904	10063.876	41.914
Quad End 2015-Oct-04	5869.137	1025.72	30.767	54549.476	9333.308	40.558
Quad End 2015-Sep-06	5887.935	1020.281	29.759	52937.118	9419.195	41.054
Quad End 2016-Apr-17	6058.832	1035.386	33.03	64915.985	11138.634	46.263
Quad End 2016-Aug-07	6236.936	1076	35.054	65259.913	12067.025	50.706
Quad End 2016-Dec-25	6888.486	1190.804	36.888	71932.466	12679.659	51.345
Quad End 2016-Feb-21	6148.142	1042.628	33.038	62084.641	11326.154	43.644
Quad End 2016-Jan-24	6238.878	1046.728	31.852	61564.938	10429.747	43.07
Quad End 2016-Jul-10	6099.703	1132.967	35.144	65744.357	12036.188	50.281
Quad End 2016-Jun-12	6111.706	1052.33	34.667	63802.055	11608.624	50.297
Quad End 2016-Mar-20	6003.069	1014.521	33.367	63433.704	11143.249	45.597
Quad End 2016-May-15	6007.697	1013.288	34.545	64151.674	11706.143	49.661
Quad End 2016-Nov-27	6821.738	1210.228	35.994	72619.228	12812.82	51.581
Quad End 2016-Oct-02	6455.888	1100.785	35.746	68048.314	11866.144	51.532
Quad End 2016-Oct-30	6511.404	1182.856	35.618	70969.909	14193.527	51.509
Quad End 2016-Sep-04	6495.276	1100.691	37.112	66800.459	11571.238	51.057
Quad End 2017-Apr-16	7425.21	1362.242	38.023	81833.92	15124.927	52.31
Quad End 2017-Feb-19	6859.972	1192.376	36.631	77287.624	13834.492	51.934
Quad End 2017-Jan-22	6613.704	1149.871	37.098	72449.465	12385.606	51.428
Quad End 2017-Mar-19	7585.067	1341.87	36.817	81206.096	14608.748	51.917

[MFelix]

Hi @jpt1228,

I don't know if this can help but if the lines are incremental so value of end of month is the sum of both lines tou can use the TOTALMTD / TOTALQTD / TOTALYTD and you can compare the periods in differents year. If the line are not summed maybe you can do it with a validation of the maximum date and then converting that line to the end of month and use the previous formulas.

To help you better can you please tell me if the two line you identify are to be sum together or consider separatly, if so wich one does count for end result the October-02 or October-30?

Regards,

MFelix

[jpt1228]

Hi @MFelix The data is for the 4 weeks ending on that date. So 4 Weeks (Reported as Quad) on Apr 16 would be data from March 20th-April 16th. In the October data example Quad End 2016-Oct-30 would include data from October 3rd 2016-October 30th. Quad End 2016-Oct-02 would include data from September 5th 2016-October 2nd 2016. If Quad end 2016-Oct-30 would be the 11th period out of 13 I want to be able to compare the 11th period of 2017 VS the 11th period 2016. I can't use the date because depending on the calendar there may be 2 reporting times in the same month.

Does this help?

Time Description	Geography	Dollars	Units	Stores
Quad End 2017-Apr-16	TOTAL US - NATURAL CHANNEL	81833.92	15124.927	52.31
Quad End 2017-Apr-16	MID-ATLANTIC - STANDARD REGION - NAT CHNL	7425.21	1362.242	38.02
Quad End 2017-Mar-19	TOTAL US - NATURAL CHANNEL	81206.096	14608.748	51.92
Quad End 2017-Mar-19	MID-ATLANTIC - STANDARD REGION - NAT CHNL	7585.067	1341.87	36.82
Quad End 2017-Feb-19	TOTAL US - NATURAL CHANNEL	77287.624	13834.492	51.93
Quad End 2017-Feb-19	MID-ATLANTIC - STANDARD REGION - NAT CHNL	6859.972	1192.376	36.63
Quad End 2017-Jan-22	TOTAL US - NATURAL CHANNEL	72449.465	12385.606	51.43
Quad End 2017-Jan-22	MID-ATLANTIC - STANDARD REGION - NAT CHNL	6613.704	1149.871	37.10
Quad End 2016-Dec-25	TOTAL US - NATURAL CHANNEL	71932.466	12679.659	51.35
Quad End 2016-Dec-25	MID-ATLANTIC - STANDARD REGION - NAT CHNL	6888.486	1190.804	36.89
Quad End 2016-Nov-27	TOTAL US - NATURAL CHANNEL	72619.228	12812.82	51.58
Quad End 2016-Nov-27	MID-ATLANTIC - STANDARD REGION - NAT CHNL	6821.738	1210.228	35.99
Quad End 2016-Oct-30	TOTAL US - NATURAL CHANNEL	70969.909	14193.527	51.51
Quad End 2016-Oct-30	MID-ATLANTIC - STANDARD REGION - NAT CHNL	6511.404	1182.856	35.62
Quad End 2016-Oct-02	TOTAL US - NATURAL CHANNEL	68048.314	11866.144	51.53
Quad End 2016-Oct-02	MID-ATLANTIC - STANDARD REGION - NAT CHNL	6455.888	1100.785	35.75

[MFelix]

Hi @jpt1228,

Taking your information I agree that the best way would be to indexed the data to the period-year and then.compare the two in different years.

Regards,
MFelix

[jpt1228]

I am a new user of PowerBI - Do you have any recommended DAX formulas, or how would I go about assigning an index period to the specific time frame?

[MFelix · Accepted solution]

Hi @jpt1228,

I was looking at your data and since you have a value every 4 weeks, I believe that the fastest way to have this is to calculate the number of the week and then divide that by 4 this will give you the 1 to 13 needed index for each row. I tried to made just with week number but due to the 29 February the number was off by one week in 2017.

Create a custom column with this formula and then use this to make the comparision between periods.

ID_ROW =
DIVIDE (
    WEEKNUM ( FORMAT ( RIGHT ( Geography[Time Description], 11 ), "dd-mm-yyyy" ) ),
    4
)

I'm assuming that your download always is with the format "Quad End 2017-Apr-16" that is why I make the extration for date values with the RIGHT formula and convert it to date.

As you can see below all the values are as you need and repeated dates in the same month assume different values.

Hope this helps,

MFelix

[jpt1228]

Hi @MFelix - Since I am new to Power BI and DAX I used the query editor to extract the date, created a year column, and week in year and divided that column by 4. Then renamed that "Quad Week Period" which is the long version of your formula.

let
Source = Excel.Workbook(File.Contents("C:\Users\Jon\Desktop\TDP Test File.xlsx"), null, true),
#"Expanded Data" = Table.ExpandTableColumn(Source, "Data", {"Column1", "Column2", "Column3"}, {"Data.Column1", "Data.Column2", "Data.Column3"}),
#"Promoted Headers" = Table.PromoteHeaders(#"Expanded Data", [PromoteAllScalars=true]),
#"Removed Other Columns" = Table.SelectColumns(#"Promoted Headers",{"Time Description", "Geography", "TDP"}),
#"Inserted Last Characters" = Table.AddColumn(#"Removed Other Columns", "Last Characters", each Text.End(Text.From([Time Description], "en-US"), 11), type text),
#"Changed Type" = Table.TransformColumnTypes(#"Inserted Last Characters",{{"Last Characters", type date}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Last Characters", "Date"}}),
#"Inserted Week of Year" = Table.AddColumn(#"Renamed Columns", "WeekOfYear", each Date.WeekOfYear([Date]), type number),
#"Inserted Division" = Table.AddColumn(#"Inserted Week of Year", "Inserted Division", each [WeekOfYear] / 4, type number),
#"Changed Type1" = Table.TransformColumnTypes(#"Inserted Division",{{"Inserted Division", Int64.Type}}),
#"Inserted Year" = Table.AddColumn(#"Changed Type1", "Year", each Date.Year([Date]), type number),
#"Renamed Columns1" = Table.RenameColumns(#"Inserted Year",{{"Inserted Division", "Quad Week Period"}})
in
#"Renamed Columns1"

One last question - How do I create a custom calculation to calculate the numerical difference from Quad Week Period 4 in 2017 VS the same Quad Week Period 4 in 2016? I have used the formula to sum the month and then pull in the same time last year like below.

If period 4 in 2016 was 10 and period 4 in 2017 was 12 I want the result to be 2 which is the difference from 12-10=2

TotalRevenueThisPeriodLY = CALCULATE(SalesDataAggregated[TotalRevenue], FILTER(ALL(Dates), Dates[CalendarYear] = MAX(Dates[CalendarYear])-1), SAMEPERIODLASTYEAR(Dates[Date]))

[MFelix]

Hi @jpt1228,

I have made a table with Week, Period, and Year and added these columns:

ID = Table2[Year]&Table2[Period]

column = IF (
          Table2[Year] = MIN ( Table2[Year] ),
          BLANK (),
          Table2[Week]
          - LOOKUPVALUE ( Table2[Week], Table2[ID], Table2[Year] - 1 & Table2[Period] )
         )

The result was this:

Regards,

MFelix