Dates table maintenance

DAX DAX Calculations
1

Is it possible to create a dynamic dates table in M , based on yearstart of MIN(Transactiondate) and yearend of MAX(Transactiondate) ?

2

@robsmi,

@Melissa or one of the forum’s other Power Query gurus I’m sure can tell you how to do this in M, but this thread walks through a couple of different ways to do it in DAX:

Dynamic Date Table (Update Date Table)

  • Brian
3

@robsmi, @BrianJ

Yes there are several ways to do this in Power Query M, some examples:
This will return the first date of that column: List.Min( YourQueryName[TransactiondateColumn])
And this will return Jan 1st of the first date in your Transactionsdate column:

StartDate = #date( Date.Year( List.Min( YourQueryName[TransactiondateColumn])), 1, 1)

This will return Dec 31 th of the last date in your Transactionsdate column:

EndDate = Date.EndOfYear( List.Max( YourQueryName[TransactiondateColumn]))

You now have defined the start- and endpoint for your calendar, so you can count the days between these dates AND add one day because you want to include the last day as well…

All put together you’re M code will look something like this:

let
  StartDate = #date( Date.Year( List.Min( YourQueryName[TransactiondateColumn])), 1, 1),
  EndDate = Date.EndOfYear( List.Max( YourQueryName[TransactiondateColumn])),
  DayCount = Duration.Days(Duration.From(EndDate - StartDate)),
  DayCountAdd = DayCount + 1,
  Source = List.Dates(StartDate,DayCountAdd,#duration(1,0,0,0))
in
  Source

I hope this is helpful
.
BTW in the course resource here you can find a full M code script for a Date table function.

2 Likes
4

Hi @robsmi, we’ve noticed that no response has been received from you since 12th of February. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Thanks!

5

Thank you so much for your support.
With help from Melissa’s example I created a simple solution where I replaced the initial parameter query with three lines defining Startdate, Stopdate and Startmonth of Fiscal Year.
StartDate = List.Min(Sales[OrderDate]),
EndDate = List.Max(Sales[OrderDate]),
FYStartMonth = 1,
As this is part of the scheduled data reload the Dates table will automatically be redefined when a new physical- or fiscal- year appears.
Thank you
/robert

6

Hi @robsmi , a similar topic was covered in this forum thread link. You might get additional tips here.