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.
Hi all,
How can I create a new date table but only consist of End of Month dates?
Thank you very much before-hand.
Solved! Go to Solution.
Here is one way to do it in a DAX calculated table. You can adapt it to start at a different month or extend more or less.
Date EOMs =
VAR nums =
GENERATESERIES (
0,
24,
1
)
RETURN
ADDCOLUMNS (
nums,
"Date",
EOMONTH (
DATE ( 2019, 1, 31 ),
[Value]
)
)
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
You can also create a new calculated table using this formula:
Calendar = filter( calendar( date(2022,1,1), date(2022,12,31)), [Date] = EOMONTH([Date], 0))
Thank you for your reply @ChrisMendoza. I have done something similar to what you suggested but now I would like to the "unrelate" the new date table from an existing Calendar table.
From an existing Date Table as:
Date = CALENDAR(DATE(2019,01,01),TODAY())
Add a Column as :
EoM Column = ENDOFMONTH('Date'[Date])
Create new table as :
EoM = DISTINCT('Date'[EoM Column])
Proud to be a Super User!
Here is one way to do it in a DAX calculated table. You can adapt it to start at a different month or extend more or less.
Date EOMs =
VAR nums =
GENERATESERIES (
0,
24,
1
)
RETURN
ADDCOLUMNS (
nums,
"Date",
EOMONTH (
DATE ( 2019, 1, 31 ),
[Value]
)
)
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Hi @mahoneypat,
Is it possible to adjust the series generation such that it will be added month by month instead of having total number of months in the series already specifically defined?
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 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |