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.
joshcomputer1
Helper V
Helper VSo let's say you have data that looks like this:
You want to clear the numbers before 2018 and after the week number (26). This can be done in the query editor. Make sure that your number is imported as text, if not click the column and change the data type to text.
Next, go to "Extract" then select Range. The starting index starts to count at 0 so if you want to start at the 5th character, then it's index 4. Then list the number of characters that you want to collect. (8)
The result will be the cleaned up date in Text. Switch the data type to Date and you should have it.