Forum Discussion
Date Table
Hi community
I have a dataset where the last entry against a date is 01.09.2022. My current date table (below) returns a date table with the last date 31.12.2022.
How would i change this so it returns the maximum date in the dataset and not the final day of the year of this date ?
Let's say the date field in your fact table is called 'FactTable'[Date]
You can limit the start and end date in the calendar table by using:
Date Table = VAR _MinDate = MIN ( FactTable[Date] ) VAR _MaxDate = MAX ( FactTable[Date] ) RETURN ADDCOLUMNS ( CALENDAR ( _MinDate, _MaxDate ), "YEAR", YEAR ( [Date] ), "Month", FORMAT ( [Date], "mmmm" ), "MONTH NUMBER", MONTH ( [Date] ) )
3 Replies
Hi Caldowd98 ,
I don´t reccomend you to limit the rows of your calendar table to the last date of your fact table. You can have problems with time intelligence functions.
See here how a calendar table must be written:
https://community.powerbi.com/t5/Community-Blog/Time-Intelligence-Calendar-Table/ba-p/1000565
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Let's say the date field in your fact table is called 'FactTable'[Date]
You can limit the start and end date in the calendar table by using:
Date Table = VAR _MinDate = MIN ( FactTable[Date] ) VAR _MaxDate = MAX ( FactTable[Date] ) RETURN ADDCOLUMNS ( CALENDAR ( _MinDate, _MaxDate ), "YEAR", YEAR ( [Date] ), "Month", FORMAT ( [Date], "mmmm" ), "MONTH NUMBER", MONTH ( [Date] ) )Thanks Paul - perfect 🙂
