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.
samdthompson
Memorable Member
Memorable MemberYeah, thats not a formula you want to rebuild. Make a date table and join on the calendar date in the data table, then just return the date table column with the format you are wanting.
The date table needs to be made up of all dates from the ealiest to the latest in your data including all the ones that you dont have data for.
First step, convert the date in your data table to a date format,
Second make a calculated table
Third, join the two.