Forum Discussion
Solved
Excel formula to DAX
Hi, I have to extract a date from a serial number. I've been doing it in Excel. Now we are migrating to Power BI I wonder if there is an easier way (measure, column?). Serial number is in this fo...
Try this:
Date.AddWeeks(#date(Character.ToNumber(Text.Middle([Test],4,1)) + 1943,1,1),Number.From(Text.Middle([Test],6,2)))
I didn't know what the XXXX's were, so I ignored them since you were able to get the year and week from the KW26.
The logic of it is this:
- Get the ASCII representation of K (75) and add 1943 to it. That will get you 2018. J would be ASCII 74+1943=2017. So that works. This ultimately returns a #date() of 1/1/2018.
- Next I grabbed the week number 26 and made that my 2nd argument in the Date.AddWeeks() function. So it adds 26 weeks to 1/1/2018 and returns 7/2/2018, which is your July 2018. Now just use whatever parts of that date you want to use.
- my [Test] field is your text field.
Thanks guys.