Using this period/last period/year ago when a period is 28 days not a month

[dykesn92]

My company reports in 28 day periods instead of months.

Currently I have my period calendar in one file "PeriodCalendar" and everything else in a file called  "Data Dump"

While I can link the queries I have also created a merged column:

= Table.ExpandTableColumn(#"Merged Queries", "PeriodCalendar", {"Period (L)"}, {"PeriodCalendar.Period (L)"})

I am trying to also create a field called last period. (And then in turn same period last year).

to do this I created a calculated field and then create another merge field.

= Table.AddColumn(#"Expanded PeriodCalendar", "Custom.1", each Date.AddDays([Date], -28))

The problem is that while this works it means I have to create 2 new columns for every version of last period that I want.

I was wondering if there was a more effiecient way of doing  this.

[dramus]

What does your PerodCalendar look like, what fields do you have?

[dykesn92]

Hi Dramus,

Just Date and period where date is every day in a format of dd/mm/yyyy and the period is PX YY

Thanks

Nick

[dramus]

Previous Period = lookupvalue('Period Dates'[Period],'Period Dates'[Date],dateadd('Period Dates'[Date],-28,DAY))

Same Period Previous Year = lookupvalue('Period Dates'[Period],'Period Dates'[Date],dateadd('Period Dates'[Date],-1,YEAR))

Does this help?

[dykesn92]

My company reports in 28 day periods instead of months.

Currently I have my period calendar in one file "PeriodCalendar" and everything else in a file called  "Data Dump"

While I can link the queries I have also created a merged column:

= Table.ExpandTableColumn(#"Merged Queries", "PeriodCalendar", {"Period (L)"}, {"PeriodCalendar.Period (L)"})

I am trying to also create a field called last period. (And then in turn same period last year).

to do this I created a calculated field and then create another merge field.

= Table.AddColumn(#"Expanded PeriodCalendar", "Custom.1", each Date.AddDays([Date], -28))

The problem is that while this works it means I have to create 2 new columns for every version of last period that I want.

I was wondering if there was a more effiecient way of doing  this.