cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular 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
Highlighted
Regular 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.

 

 

Highlighted
Responsive Resident
Responsive Resident

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?

Highlighted
Regular 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

Highlighted
Responsive Resident
Responsive Resident

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?

Helpful resources

Announcements
Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Upcoming Events

Community Summit North America – Join Online!

Join this community-driven Power Platform digital event for unbiased support and problem-solving.

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Top Solution Authors