Forum Discussion
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?
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
5 Replies
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
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