cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
dykesn92 Frequent Visitor
Frequent Visitor

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

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.

 

 

4 REPLIES 4
dykesn92 Frequent Visitor
Frequent Visitor

Day to day comparison against same period last year.

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 Established Member
Established Member

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

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

dykesn92 Frequent Visitor
Frequent Visitor

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

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 Established Member
Established Member

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

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?