cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
kashmachine Frequent Visitor
Frequent Visitor

Adding dates to existing rows in table

I currently have a table with the following:

ID

Category

Failure Rate

1

A

0.5

2

B

0.2

3

C

0.1

 

I need to add a date range of month-year for each row on the table so it would become

ID

Category

Failure Rate

Date

1

A

0.5

Jan-17

1

A

0.5

Feb-17

1

A

0.5

March-17

1

A

0.5

Apr-17

1

A

0.5

May-17

1

A

0.5

Jun-17

1

A

0.5

Jul-17

1

A

0.5

Aug-17

1

A

0.5

Sept-17

1

A

0.5

Oct-17

1

A

0.5

Nov-17

1

A

0.5

Dec-17

2

B

0.2

Jan-17

2

B

0.2

Feb-17

2

B

0.2

March-17

2

B

0.2

Apr-17

2

B

0.2

May-17

2

B

0.2

Jun-17

2

B

0.2

Jul-17

2

B

0.2

Aug-17

2

B

0.2

Sept-17

2

B

0.2

Oct-17

2

B

0.2

Nov-17

2

B

0.2

Dec-17

3

C

0.1

Jan-17

3

C

0.1

Feb-17

3

C

0.1

March-17

3

C

0.1

Apr-17

3

C

0.1

May-17

3

C

0.1

Jun-17

3

C

0.1

Jul-17

3

C

0.1

Aug-17

3

C

0.1

Sept-17

3

C

0.1

Oct-17

3

C

0.1

Nov-17

3

C

0.1

Dec-17


The reason for the above is to achieve something with the following pseudo code:

IF DATE is BETWEEN Jan-17 and Dec-19 THEN DIVIDE failure rate by 24
ELSE IF DATE is BETWEEN Dec-19 to Dec-21 THEN DIVIDE failure rate by 12

Any ideas?

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Adding dates to existing rows in table

hi, @kashmachine 

If so, try this formula

Table= var _datetable=FILTER(CALENDAR("2017-01-01","2017-12-31"),DAY([Date])=1) return

GENERATE(Basic,SELECTCOLUMNS(_datetable,"Mon-year",FORMAT([Date],"MMM-YYYY")))

https://docs.microsoft.com/en-us/dax/pre-defined-date-and-time-formats-for-the-format-function

If so, [Mon-year] is a text type, ("Jan-2017" not a date format).

I would suggest you use above formula to create the table and change the format in modeling. In this case, it still the date type.

 

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
Community Support Team
Community Support Team

Re: Adding dates to existing rows in table

hi, @kashmachine 

You could just use this formula to create a "New Table"

Table = GENERATE(Basic,FILTER(CALENDAR("2017-01-01","2017-12-31"),DAY([Date])=1))

Result:

1.JPG

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
kashmachine Frequent Visitor
Frequent Visitor

Re: Adding dates to existing rows in table

Hi Lin,

So I've created a new table and used your formula. I think I'm almost there but how do I get the syntax of the date to appear "Jan-2017" so "MMM-YYYY"


Community Support Team
Community Support Team

Re: Adding dates to existing rows in table

hi, @kashmachine 

If so, try this formula

Table= var _datetable=FILTER(CALENDAR("2017-01-01","2017-12-31"),DAY([Date])=1) return

GENERATE(Basic,SELECTCOLUMNS(_datetable,"Mon-year",FORMAT([Date],"MMM-YYYY")))

https://docs.microsoft.com/en-us/dax/pre-defined-date-and-time-formats-for-the-format-function

If so, [Mon-year] is a text type, ("Jan-2017" not a date format).

I would suggest you use above formula to create the table and change the format in modeling. In this case, it still the date type.

 

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

kashmachine Frequent Visitor
Frequent Visitor

Re: Adding dates to existing rows in table

Worked perfectly, you're a star Lin!

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 339 members 3,072 guests
Please welcome our newest community members: