Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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
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 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"
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
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
Worked perfectly, you're a star Lin!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |