Adding dates to existing rows in table

[Anonymous]

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?

[v-lili6-msft]

hi, @Anonymous 

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:

Best Regards,

Lin

[Anonymous]

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"

[v-lili6-msft · Accepted solution]

hi, @Anonymous 

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

[Dina17]

Hello Lin,

maybe you could help me as well. I have a similar problem, but I have a specific year that needs to be add to every row. That's what I have:

Name	2018	2019	2020
A	45	42	768
B	31	13	432
C	45	78	43
D	42	46	65

And that's what I need:

Name	Total	Year
A	45	2018
B	31	2018
C	45	2018
D	42	2018
A	42	2019
B	13	2019
C	78	2019
D	46	2019
A	768	2020
B	432	2020
C	43	2020
D	65	2020

It'd be great if it worked automatically.

Thanks,

Dina

[Anonymous]

Worked perfectly, you're a star Lin!