How can I create 14 day periods in my calendar table?

[ERing]

I have a basic calendar table and I'm looking for a way to add 14 day periods which start on a Wednesday and end on the Tuesday that is 14 days later. Below is an example of what I'm looking to have.

Is there anyway I can add the Week_Start and Week_Start_Number to my calendar table? I would prefer to do this in Power Query if possible but willing to use DAX as well. 

[v-pnaroju-msft]

Hi ERing,

We want to check if the details we gave have solved your problem.
If you need more help, you can kindly reach out to the Microsoft Fabric community.

Thank you.

[v-pnaroju-msft]

Thank you @lbendlin, for your response.

Hi ERing,

We would like to kindly inquire whether you have had the opportunity to review the solution provided by @lbendlin  to resolve the issue. Additionally, please find attached the screenshot and sample PBIX file based on the initial data provided:

We hope the information furnished herewith helps in clarifying your query. Should you have any further questions or require additional assistance, please feel free to reach out to the Microsoft Fabric community.

Thank you.

[v-pnaroju-msft · Accepted solution]

Thankyou, @lbendlin, @Ashish_Mathur for your response.

Hi ERing,

We appreciate your question on the Microsoft Fabric Community Forum.

Based on my understanding of the issue, please find attached a screenshot and a sample PBIX file that may assist in resolving the matter:

We hope the information provided helps to resolve the issue.
Should you have any further queries, kindly feel free to contact the Microsoft Fabric community.

Thank you.

[ERing]

@v-pnaroju-msft Thank you, but this does not give me what I need.

In the example you provided, Date 7/1/2024 & 7/2/2024 are a Monday and Tuesday. They should actually have a Week_Start date of 6/26/2024 (the previous Wednesday). 7/1/2024 & 7/2/2024 should also have a Week_Start_Number of 1. 7/3/2024 - 7/16/2024 would have a Week_Start of 7/3/2024 and a  Week_Start_Number of 2. 

I've attached a sample file below where the first date in a the Calendar is a Sunday (1/1/2023). In this example, 1/1/2023 - 1/3/2023 would have a Week_Start of 12/28/2022 (the previous Wednesday) and 1/1/2023 - 1/3/2023 would have a Week_Start_Number of 1. 1/4/2023 - 1/17/2023 would have a Week_Start of 1/4/2023 and a Week_Start_Number of 2. 1/18/2023 - 1/31/2023 would have a Week_Start of 1/18/2023 and a Week_Start_Number of 3.


Custom 14 Day Count Period Starts Example 

[Ashish_Mathur]

Hi,

Write this calculated column formula to create the week_start date column

Week_start = if(day(calendar[date])<=15,date(year(calendar[date]),month(calendar[date]),1),date(year(calendar[date]),month(calendar[date]),15))

Hope this helps.

[ERing]

@Ashish_Mathur Thanks. This is close, but not exactly what I need.

My goal is to have something that resets after 14 days (on every 15th day). So the periods should start on 1/15/2020, 1/29/2020, 2/11/2020. etc. 

[lbendlin]

14 day period = 1 + Int64.From(Date.WeekNumber([Date])/2)

[ERing]

This would be a custom column in PQ, rigtht? I tried adding this but I get an error message.

[lbendlin]

let
    Source = List.Dates(#date(2025,1,1),100,#duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Week_Start_Number", each Int64.From(Date.DayOfYear([Date])/14+0.49))
in
    #"Added Custom"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the entire Source step with your own source.

[ERing]

Thanks @lbendlin This definitley gets me closer, but a few additional questions.

1. How can I add the Week_Start (should have called it Period_Start) column in original post? The intent is to show the First Date in each 14 day period.

2. It looks like I can change the number "14" to any number I wish and it will give me that number of day as intervals when assigning a Week_Start_Number. Does that sound correct?

3. My calendar table code is below. It looks like I can simply add in the last line of code you provided before the "in" in my code to get the Week_Start_Number. Does that sound correct?

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUByIjAyMDpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [StartDate = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"StartDate", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "EndDate", each Date.From(DateTime.LocalNow())),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"EndDate", type date}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type1", "Date", each {Number.From([StartDate])..Number.From([EndDate])}),
    #"Expanded Date" = Table.ExpandListColumn(#"Added Custom1", "Date"),
    #"Changed Type2" = Table.TransformColumnTypes(#"Expanded Date",{{"Date", type date}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type2",{"StartDate", "EndDate"}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Removed Columns", "Date", "Date - Copy"),
    #"Extracted Year" = Table.TransformColumns(#"Duplicated Column",{{"Date - Copy", Date.Year, Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Extracted Year",{{"Date - Copy", "Year"}})
in
    #"Renamed Columns"

#"Added Custom" = Table.AddColumn(#"Changed Type", "Week_Start_Number", each Int64.From(Date.DayOfYear([Date])/14+0.49)

[lbendlin]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUByIjAyMDpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [StartDate = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"StartDate", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "EndDate", each Date.From(DateTime.LocalNow()),type date),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Date", each {Number.From([StartDate])..Number.From([EndDate])}),
    #"Expanded Date" = Table.ExpandListColumn(#"Added Custom1", "Date"),
    #"Changed Type2" = Table.TransformColumnTypes(#"Expanded Date",{{"Date", type date}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type2",{"StartDate", "EndDate"}),
    #"Added Custom2" = Table.AddColumn(#"Removed Columns", "Year", each Date.Year([Date]), Int64.Type),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Period_Start", each Int64.From(Date.DayOfYear([Date])/14+0.49),Int64.Type)
in
    #"Added Custom3"

Note that the counter resets every year.