Hi Guys,
Birthday.xlsx (17.4 KB)
I am trying to calculate the number of days till the next b-day in the query editor.
I run into an issue if the person has the bday on 29/02.
Could you please help me to resolve the issue?
Please find the attached file.
Thank you,
Mateusz
Hi @Matty. As this is a forum for Power BI issues, please upload your work-in-progress PBIX file plus a marked-up screenshot and/or Excel mockup of the visual of interest showing your desired outcome.
Greg
Hi @Matty, as mentioned by @Greg above, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.
I also suggest that you check the forum guideline How To Use The Enterprise DNA Support Forum. Not adhering to it may sometimes cause delay in getting an answer.
Please also check the How To Mask Sensitive Data thread for some tips on how to mask your pbix file.
@Greg Apologies,
Bday.pbix (18.9 KB) Birthday.xlsx (127.4 KB)
Please find the corrected files.
As I mentioned I run into an issue while calculating the next coming bday for the person. The error occurs when someone is born 29/02.
I used below code to calcualte next bday.
let
Source = Excel.Workbook(File.Contents(“C:\Users\MWojnach\Documents\Project\Birthday.xlsx”), null, true),
Table1_Table = Source{[Item=“Table1”,Kind=“Table”]}[Data],
#“Changed Type” = Table.TransformColumnTypes(Table1_Table,{{“Dob”, type date}}),
#“Added Custom Column” = Table.AddColumn(#“Changed Type”, “Custom”, each Text.Combine({Date.ToText([Dob], “dd”), “-”, Date.ToText([Dob], “MMM”)}), type text),
#“Changed Type1” = Table.TransformColumnTypes(#“Added Custom Column”,{{“Custom”, type date}}),
#“Added Custom” = Table.AddColumn(#“Changed Type1”, “Today”, each DateTime.Date(DateTime.LocalNow())),
#“Added Custom1” = Table.AddColumn(#“Added Custom”, “Diff”, each [Custom]-[Today]),
#“Changed Type2” = Table.TransformColumnTypes(#“Added Custom1”,{{“Diff”, Int64.Type}, {“Today”, type date}}),
#“Added Custom2” = Table.AddColumn(#“Changed Type2”, “NextBday”, each if [Diff] < 0 then Date.AddYears([Custom],1) else [Custom]),
#“Added Custom3” = Table.AddColumn(#“Added Custom2”, “NextB-day days”, each [NextBday]-[Today]),
#“Changed Type3” = Table.TransformColumnTypes(#“Added Custom3”,{{“NextB-day days”, Int64.Type}})
in
#“Changed Type3”
The errors occur in applied steps (step Changed Type1) I understand why it happened as 29/02/2021 doesn’t exist.
I will appreciate it if you could guide me/ give me a hint on how could I resolve this error.
I thought to add additional logic such as if the year is ending 1 then add 3, if 2 then add 2 but then we would run into an issue if the year is ending 0 then you would add 4 or would add 0.
2020, 2024, 2028, 2032,2036,2040
Thank you,
Mateusz
Hi @Matty,
Give something like this a go, just paste the code in a New Blank Query. You didn’t state how you wanted to handle birthdays on the 29th so I moved them a day forward.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TcfJCQAgDATAXvJ2YRPP1BLSfxuKfoT5TIRQcXiTLCHmoIH8staNdqhh6ksFB0a/4UmFu2Ru", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Dob = _t]),
ChType = Table.TransformColumnTypes(Source,{{"Dob", type date}}),
NextBday = Table.AddColumn(ChType, "Next Bday", each
let Today = Date.From(DateTime.FixedLocalNow()),
nDate = try #date( Date.Year( Today ), Date.Month([Dob]), Date.Day([Dob])) otherwise #date( Date.Year( Today ), 3, 1)
in
if nDate < Today
then try #date( Date.Year( Today )+1, Date.Month([Dob]), Date.Day([Dob])) otherwise #date( Date.Year( Today )+1, 3, 1)
else nDate
)
in
NextBday
I hope this is helpful
Thank you, I give it a go and I changed only one thing:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(“TcfJCQAgDATAXvJ2YRPP1BLSfxuKfoT5TIRQcXiTLCHmoIH8staNdqhh6ksFB0a/4UmFu2Ru”, BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Dob = _t]),
ChType = Table.TransformColumnTypes(Source,{{“Dob”, type date}}),
NextBday = Table.AddColumn(ChType, “Next Bday”, each let Today = Date.From(DateTime.FixedLocalNow()),
nDate = try #date( Date.Year( Today ), Date.Month([Dob]), Date.Day([Dob])) otherwise #date( Date.Year( Today )+3, 2, 29)
in
if nDate < Today
then try #date( Date.Year( Today )+1, Date.Month([Dob]), Date.Day([Dob])) otherwise #date( Date.Year( Today )+1, 3, 1)
else nDate)
in
NextBday
I changed to 2024 as I would like to find out when the next bday will fall 
Saying that I could slightly change my m-code if the date and month = 29/02 then add the year 2024 otherwise apply the rest of logic
Thank you,
Matty
let Today = Date.From(DateTime.FixedLocalNow()),
nDate = try #date( Date.Year( Today ), Date.Month([Dob]), Date.Day([Dob])) otherwise #date( Date.Year( Today ), 3, 1)
in
if nDate < Today
then try #date( Date.Year( Today )+1, Date.Month([Dob]), Date.Day([Dob])) otherwise #date( Date.Year( Today )+1, 3, 1)
else nDate
)
in
NextBday
Could you please suggest where I could read on to have a better understanding of the functions in the m-code. (with some examples)
Correct me If I am wrong but
Function1 =Today = Date.From(DateTime.FixedLocalNow())
Function2 = nDate = try #date( Date.Year( Today ), Date.Month([Dob]), Date.Day([Dob])) otherwise #date( Date.Year( Today ), 3, 1)
but then Function2 is diveded to “try” and “otherwise”?
I am trying to understand the part with “try” and “otherwise”
Am I right to say “try” is the first pattern in function 2 that should work and if the error will occur in the function pattern 1 then the formula will go to the 2nd stage which is “otherwise”?
Thank you!
Matty
Hi @Matty,
I’ve created a post on that, you can find it here:
Thank you @Melissa!