Date Table identifying D-10 to D+10

[PS_78]

Hello - I have below requirement. Can you please help with this?

I have created a table to hold "Dates" from 2022 until 2030 in Power Query.

An excel sheet is maintained for Year, Month, Days Backward and Days Forward. I want to calculate D-Days Backward days and D+Days Forward days. For example if for April 2024, Days Backward = 10 and Days Forward = 20, I would need to build table in Power BI like below. I need to update D-10 for 3/18, D-9 for 3/19... D-6 for 03/22, D-6 for 03/23 (Sat), D-6 for 03/24 (Sun)... D19 to 04/25, D20 to 4/26, D20 to 04/27 (Sat) and D20 to 04/28 (Sun). Please check yellow highlighted for Saturday and Sunday

Thanks,

Phani

[PS_78]

Thanks for the response @v-jianpeng-msft . I have handled it in Power Query.

Regards,

Phani

[v-jianpeng-msft]

Hi, @PS_78 

Thank you very much for your reply. It's great to hear that your problem has been solved. Would you like to be able to share your solution here that doesn't include your private data, so that others in the community can quickly find answers to the same questions. After that, you can mark your reply as a solution.

Best Regards

Jianpeng Li

[PS_78 · Accepted solution]

That's a nice idea. Thanks @v-jianpeng-msft for your suggestion. I am giving the code from Advanced editor as I can't attach files here in the community. We can definitely simply by combining multiple steps into one. But for clarity I have used multiple steps. Also I hard coded days backward and days forward in this code. We can replace it with a file.

Begin of Code from Advanced Editor

let
ME_CloseDays = {Number.From(#date(2022,1,1))..Number.From(#date(2030,12,31))},
#"Converted to Table" = Table.FromList(ME_CloseDays, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
#"New Column - Year_Month" = Table.AddColumn(#"Changed Type", "Year_Month", each Number.ToText( Date.Year( [Date] ) ) & "_" & Text.Start( Date.MonthName( [Date] ), 3 )),
#"New Column - DayofWeek" = Table.AddColumn(#"New Column - Year_Month", "DayofWeek", each Date.DayOfWeekName([Date])),
#"Added Custom" = Table.AddColumn(#"New Column - DayofWeek", "WeekDay?", each Date.DayOfWeek([Date])),
#"New Column - FirstDayoftheMonth" = Table.AddColumn(#"Added Custom", "FirstDayoftheMonth", each Date.StartOfMonth([Date])),
#"New Column - LastDayoftheMonth" = Table.AddColumn(#"New Column - FirstDayoftheMonth", "LastDayoftheMonth", each Date.EndOfMonth( [Date] )),
#"New Column - DaysBackward" = Table.AddColumn(#"New Column - LastDayoftheMonth", "DaysBackward", each "10"),
#"New Column - DaysForward" = Table.AddColumn(#"New Column - DaysBackward", "DaysForward", each "20"),
#"Changed Type1" = Table.TransformColumnTypes(#"New Column - DaysForward",{{"DaysBackward", Int64.Type}, {"DaysForward", Int64.Type}}),
#"New Column - Backward/Forward?" = Table.AddColumn(#"Changed Type1", "Backward/Forward?", each if Duration.Days([LastDayoftheMonth] - [Date]) < [DaysBackward] then
"Backward"
else if Duration.Days([Date] - [FirstDayoftheMonth]) < [DaysForward] then
"Forward"
else
""),
#"Filtered Rows" = Table.SelectRows(#"New Column - Backward/Forward?", each ([#"Backward/Forward?"] <> "")),
#"New Column - Close Days" = Table.AddColumn(#"Filtered Rows", "Close Days", each if [#"Backward/Forward?"] = "Backward" then
"D" & Number.ToText(Duration.Days([Date] - [LastDayoftheMonth]) - 1)
else if [#"Backward/Forward?"] = "Forward" then
"D" & Number.ToText(Duration.Days([Date] - [FirstDayoftheMonth]) + 1)
else
null),
#"Filtered Rows1" = Table.SelectRows(#"New Column - Close Days", each ([Year_Month] = "2024_Apr" or [Year_Month] = "2024_Mar")),
#"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows1",{"Date", "Close Days", "Year_Month", "DayofWeek", "FirstDayoftheMonth", "LastDayoftheMonth", "DaysBackward", "DaysForward", "Backward/Forward?"})
in
#"Reordered Columns"

End of Code from Advanced Editor

Thanks,

Phani

[v-jianpeng-msft]

Hi, @PS_78 

Maybe you can try the following DAX expressions:

Days Backward = DATEADD(DateTable[Date], -1 * RELATED(ExcelSheet[Days Backward]), DAY)

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.