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
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Upcoming Events

Upcoming Events

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

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors