cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

## Calculation birthday from social security number (danish)

Hi,

I need to calculate the birthday of the people in my dataset from their social security number. This is a 10-digit number where the first 6 digits date of birth. for example, 051289XXXX would be 5th of December and (probably) 1989.
However because only 2 digits are used to describe year, I need to use the following 4 digits to determine century of birth.

This can be determined as follows

• If the 7th digit is 0, 1, 2 or 3 the person is from the 19th century
• If the 7th digit is 4 or 9 and the year indicator in the first 6 digits are <36 the person is from the 19th century. If they are =<36 the person is from the 20th century.
• If the 7th digit is 5, 6, 7 or 8 and the year indicator in the first 6 digits are =>36 the person is from the 20th century. If they are <=58 the person is from the 19th century.

In excel I would do something like this. But how do is express this in PowerBi advanced editor?

Public Function CprToDato(cpr As String) As Date

Dim bytCent As Byte
Dim bytSevdig As Byte
Dim bytCprYear As String

If Not IsNull(cpr) Then

bytSevdig = Mid(cpr, 8, 1)
bytCprYear = Mid(cpr, 5, 2)

Select Case bytSevdig
Case 0 To 3
bytCent = 19
Case 4, 9
If bytCprYear <= 36 Then
bytCent = 20
Else
bytCent = 19
End If
Case 5 To 8
If bytCprYear <= 36 Then
bytCent = 20
ElseIf bytCprYear >= 58 Then
bytCent = 18
End If
End Select

CprToDato = Left(cpr, 2) & "-" & Mid(cpr, 3, 2) & "-" & bytCent & bytCprYear
End If

End Function

Thanks!

1 ACCEPTED SOLUTION
MVP

check this.

``````let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjA1NLKwNI2IiFCKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"social security number" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"social security number", type text}}),
#"Extract Day" = Table.AddColumn(#"Changed Type", "Day", each Number.FromText(Text.Start([social security number], 2)), type number),
#"Extract Month" = Table.AddColumn(#"Extract Day", "Month", each Number.FromText(Text.Middle([social security number], 2, 2)), type number),
#"Extract Year" = Table.AddColumn(#"Extract Month", "Year", each Number.FromText(Text.Middle([social security number], 4, 2)), type number),
#"7th" = Table.AddColumn(#"Extract Year", "7th", each Number.FromText(Text.Middle([social security number], 6, 1)), type number),
Century = Table.AddColumn(#"7th", "century", each if [7th] <= 3
then 1900
else if ([7th] = 4 or [7th] = 9) and [Year] <= 36
then 2000
else if ([7th] = 4 or [7th] = 9) and [Year] > 36
then 1900
else if ([7th] = 5 or [7th] = 😎 and [Year] <= 36
then 2000
else if ([7th] = 5 or [7th] = 😎 and [Year] >= 58
then 1800
else 0, type number),
Birthdate = Table.AddColumn(Century, "Birthdate", each #date([century]+[Year],[Month],[Day]), type date)
in
Birthdate``````
Please mark my post as solution, this will also help others.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."

5 REPLIES 5
Community Champion

take a look at the following calculated column:

Regards FrankAT

Frequent Visitor

Thank you @FrankAT  this works perfectly!
I accepted @mwegener's reply as the solution, because I wrote I was looking for an M syntax solution.

MVP

check this.

``````let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjA1NLKwNI2IiFCKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"social security number" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"social security number", type text}}),
#"Extract Day" = Table.AddColumn(#"Changed Type", "Day", each Number.FromText(Text.Start([social security number], 2)), type number),
#"Extract Month" = Table.AddColumn(#"Extract Day", "Month", each Number.FromText(Text.Middle([social security number], 2, 2)), type number),
#"Extract Year" = Table.AddColumn(#"Extract Month", "Year", each Number.FromText(Text.Middle([social security number], 4, 2)), type number),
#"7th" = Table.AddColumn(#"Extract Year", "7th", each Number.FromText(Text.Middle([social security number], 6, 1)), type number),
Century = Table.AddColumn(#"7th", "century", each if [7th] <= 3
then 1900
else if ([7th] = 4 or [7th] = 9) and [Year] <= 36
then 2000
else if ([7th] = 4 or [7th] = 9) and [Year] > 36
then 1900
else if ([7th] = 5 or [7th] = 😎 and [Year] <= 36
then 2000
else if ([7th] = 5 or [7th] = 😎 and [Year] >= 58
then 1800
else 0, type number),
Birthdate = Table.AddColumn(Century, "Birthdate", each #date([century]+[Year],[Month],[Day]), type date)
in
Birthdate``````
Please mark my post as solution, this will also help others.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."

New Member

565-21-3304

Frequent Visitor

Thank you! This solved my problem.

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors