Forum Discussion
Create date table based on oldest date in another table
I am trying to create a date table that starts at the beginning of 2018 and ends on the oldest date of another table.
Below is the forrmula I'm using in my table. The problem is that currently the date table is going to the end of 2020 but the oldest date I have in the table 'Headcount[Report Date] is 10/31/2020. So I want my date table to end at 10/31/2020 (not 12/31/2020 which is what it's dong now). I think the issue is that I'm using the formula Year() and I need to replace this once a specific date but I'm not sure how.
How do I adjust this so my date table ends and the last date of the 'Headcount[Report Date] table?
Anonymous
Try the following table:Date Table = VAR MinYear = 2018 VAR MaxDate = Max('Headcount'[Report Date]) RETURN ADDCOLUMNS ( CALENDAR(DATE(MinYear,1,1), EOMONTH(MaxDate,0)), "Year", YEAR ( [Date] ), "Month Name", FORMAT ( [Date], "mmm" ), "Month Number", MONTH ( [Date] ), "Weekday", FORMAT ( [Date], "dddd" ), "Weekday number", WEEKDAY( [Date] ), "Quarter", "Q" & TRUNC ( ( MONTH ( [Date] ) - 1 ) / 3 ) + 1, "Today", FORMAT( Today () , "MMMM DD, YYYY" ))if you need the exact date from the headcount table the CALENDAR function should be
CALENDAR(DATE(MinYear,1,1), MaxDate),
________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
1 Reply
Anonymous
Try the following table:Date Table = VAR MinYear = 2018 VAR MaxDate = Max('Headcount'[Report Date]) RETURN ADDCOLUMNS ( CALENDAR(DATE(MinYear,1,1), EOMONTH(MaxDate,0)), "Year", YEAR ( [Date] ), "Month Name", FORMAT ( [Date], "mmm" ), "Month Number", MONTH ( [Date] ), "Weekday", FORMAT ( [Date], "dddd" ), "Weekday number", WEEKDAY( [Date] ), "Quarter", "Q" & TRUNC ( ( MONTH ( [Date] ) - 1 ) / 3 ) + 1, "Today", FORMAT( Today () , "MMMM DD, YYYY" ))if you need the exact date from the headcount table the CALENDAR function should be
CALENDAR(DATE(MinYear,1,1), MaxDate),
________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂