Excel formula to DAX

[macemit]

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 format: xxxxKW26xxxxx (populating A2), with K representing the year (K=2018, J=2017, I=2016, H=2015, etc) , and W26 as the week.

Formula I've been using is: =IFERROR(DATE(YEAR(IF(LEN(a2)=13,DATE(IF(MID(a2,5,1)="J",2017,IF(MID(a2,5,1)="K",2018,IF(MID(a2,5,1)="C",2010,IF(MID(a2,5,1)="D",2011,IF(MID(a2,5,1)="E",2012,IF(MID(a2,5,1)="F",2013,IF(MID(a2,5,1)="G",2014,IF(MID(a2,5,1)="H",2015,"")))))))),1,1)+(MID(a2,7,2))*7,"-")),MONTH(IF(LEN(a2)=13,DATE(IF(MID(a2,5,1)="J",2017,IF(MID(a2,5,1)="K",2018,IF(MID(a2,5,1)="C",2010,IF(MID(a2,5,1)="D",2011,IF(MID(a2,5,1)="E",2012,IF(MID(a2,5,1)="F",2013,IF(MID(a2,5,1)="G",2014,IF(MID(a2,5,1)="H",2015,"")))))))),1,1)+(MID(a2,7,2))*7,"")),1),""). 

It looks at the serial number in A2, and spits out the date. The above sample serial would give Jul-18.

If anyone can help, thanks.

[macemit]

Thanks guys.

[joshcomputer1]

So 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. 

[edhans · Accepted solution]

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]

Yeah, 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.