Dynamic Date Table using M

Power BI Data Model
1

Good day Enterprise DNA community,

I have run into this question very often in the forum, so I would like to share with you a trick I use at M to get a Dynamic Date table using your sales/fact table:

First you create a Normal date table:

image
image.png1606x197 16.6 KB

After you create your normal table, you use the following M syntax in the source of your date table:

image
image.png1720x198 22.5 KB

List.Min(FactTable[DateColumn]) => this will give you the start date of your FactTable data

List.Sum({List.Max(FactTable[DateColumn]), #duration(30, 0, 0, 0)}) => this will get the last date on your FactTable and #duration will add the amount of days you decide.

Also, be careful in always adding {} inside the List.Sum, otherwise you will get an error.

Hope this help you build a dynamic table of your model.

Best Regards,

Jorge Galindo

1 Like
2

Awesome work on this.

Sorry I actually only just saw it in the suggested topics at the bottom of another forum post.

Really nice and simple solution.

Chrs
Sam

3

Nice. @JAGP

I always like my EndDate to be December 31st, sometimes I need an additional year for instance when we want to look ahead at planned orders etc. The M code below does the following:
EndDate returns December 31st of the current year and adds one year
DayCount counts the days between the StartDate and EndDate
DayCountAdd adds 1 day because you don’t need days between but you need that last day as well
then Source creates a list of all days.

EndDate = Date.AddYears(Date.EndOfYear(Date.From(DateTime.FixedLocalNow())),1),
DayCount = Duration.Days(Duration.From(EndDate - StartDate)),
DayCountAdd = DayCount + 1,
Source = List.Dates(StartDate,DayCountAdd,#duration(1,0,0,0))