cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## 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 ?

Date Table =
CALENDARAUTO(),
"YEAR", YEAR([Date]),
"Month", FORMAT([Date], "mmmm"),
"MONTH NUMBER", MONTH([Date]))

Thanks!! 🙂
1 ACCEPTED SOLUTION
Community Champion

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
CALENDAR ( _MinDate, _MaxDate ),
"YEAR", YEAR ( [Date] ),
"Month", FORMAT ( [Date], "mmmm" ),
"MONTH NUMBER", MONTH ( [Date] )
)
``````

In doing so, you are also helping me. Thank you!

Proud to be a Super User!

3 REPLIES 3
Community Champion

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
CALENDAR ( _MinDate, _MaxDate ),
"YEAR", YEAR ( [Date] ),
"Month", FORMAT ( [Date], "mmmm" ),
"MONTH NUMBER", MONTH ( [Date] )
)
``````

In doing so, you are also helping me. Thank you!

Proud to be a Super User!

Helper I

Thanks Paul - perfect 🙂

Solution Sage

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.