Dynamic Date Table using M


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.png1606x197 16.6 KB

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

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