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 th...
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 š
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 š