Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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!
User | Count |
---|---|
86 | |
82 | |
68 | |
67 | |
55 |
User | Count |
---|---|
123 | |
100 | |
90 | |
83 | |
66 |