Reply
Senior Member
Posts: 336
Registered: ‎09-14-2016
Accepted Solution

Create date columm from odd text format

[ Edited ]

Hi all,

 

I'm running into an issue trying to create a date from an odd format.

 

From the below table, we have Year, Week Num of Year, and Day of Week Num. I need to turn the first row into February 1, 2018 and increment from there.

 

I've created a custom column of Year&Week&Day in an attempt to make a Column From Example, but I can't get it to switch to 2/8/2018 once we increment to week 2.

 

Any assistance?

 

3.png


Accepted Solutions
Super User
Posts: 2,618
Registered: ‎11-29-2015

Re: Create date columm from odd text format

HI @RMDNA

 

Please give this calculated column a try

 

Column = 
VAR BaseDate = DATE(2018,2,1)
RETURN 
    BaseDate + 
    ('Table 2'[DayOfWeekNum]-1) +
    ('Table 2'[WeekNum] * 7) - 7 

image.png

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post


All Replies
Highlighted
Super User
Posts: 2,618
Registered: ‎11-29-2015

Re: Create date columm from odd text format

HI @RMDNA

 

What day of the week does your 1 relate to?  Is 1 a Monday or a Sunday?


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Senior Member
Posts: 336
Registered: ‎09-14-2016

Re: Create date columm from odd text format

February 1 2018 was a Thursday.

Super User
Posts: 2,618
Registered: ‎11-29-2015

Re: Create date columm from odd text format

Cool, how would I know that normally?  Can I hardcode that into the logic, or does the DAX need to work out other years too.

 

How come you need to start from 1st Feb (and not 1st Jan?)... just curious


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Senior Member
Posts: 336
Registered: ‎09-14-2016

Re: Create date columm from odd text format

[ Edited ]

The end goal of creating this date key to connect this table into my report's existing calendar table.  I'm using 2018 as an example, but the data for this particular table begins 2/1/2017.

 

The client's fiscal year begins on February 1st, for whatever odd reason. It's definitely caused some reporting issues...

Super User
Posts: 2,618
Registered: ‎11-29-2015

Re: Create date columm from odd text format

It should be easy enough to generate dates from those columns for 2018, I'm just worried about 2019.  Can I assume that it will start on the 1st of Feb and in that case - and I note that the 1st of Feb will be a Friday in 2019, will that sill be WeekDayNumber = 1?


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Senior Member
Posts: 336
Registered: ‎09-14-2016

Re: Create date columm from odd text format

At least for the purposes of this example and getting a starting point, we can limit it to 2018 for now. Sounds like you might have an idea for that?

Super User
Posts: 2,618
Registered: ‎11-29-2015

Re: Create date columm from odd text format

HI @RMDNA

 

Please give this calculated column a try

 

Column = 
VAR BaseDate = DATE(2018,2,1)
RETURN 
    BaseDate + 
    ('Table 2'[DayOfWeekNum]-1) +
    ('Table 2'[WeekNum] * 7) - 7 

image.png

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Senior Member
Posts: 336
Registered: ‎09-14-2016

Re: Create date columm from odd text format

That seems to have worked. Scaling it out to additional years will be a future issue, but this should do for now. Thanks for the support.

Super User
Posts: 2,618
Registered: ‎11-29-2015

Re: Create date columm from odd text format

Hi @RMDNA

 

If I understand  your data correctly, scaling it out to other years could be as easy as the following tweak

 

Column = 
VAR BaseDate = DATE('Table 2'[Year],2,1)
RETURN 
    BaseDate + 
    ('Table 2'[DayOfWeekNum]-1) +
    ('Table 2'[WeekNum] * 7) - 7 

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!