Date Table - how do I change it to get TODAY's date and avoid manual updates

Power Query
1

Hi All - I am using the Enterprise DNA date table for my reports.
I am manually changing the date table daily to reflect my data set. Is there a way in which I can get the date table to be a dynamic one.
The start date of my dataset is the 31 Dec 2019 and i get daily updates

See the enterprise DNA date table I am using below. Please can you tell me which areas I need to adjust in the table in order to get my daily updates and avoid manual changes. Many thanks

= (StartDate as date, EndDate as date, FYStartMonth as number) as table =>
let
DayCount = Duration.Days(Duration.From(EndDate - StartDate)) +1 ,
Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),
ChangedType = Table.TransformColumnTypes(TableFromList,{{“Column1”, type date}}),
RenamedColumns = Table.RenameColumns(ChangedType,{{“Column1”, “Date”}}),
InsertYear = Table.AddColumn(RenamedColumns, “Year”, each Date.Year([Date]),type text),
InsertYearNumber = Table.AddColumn(RenamedColumns, “YearNumber”, each Date.Year([Date])),
InsertQuarter = Table.AddColumn(InsertYear, “QuarterOfYear”, each Date.QuarterOfYear([Date])),
InsertMonth = Table.AddColumn(InsertQuarter, “MonthOfYear”, each Date.Month([Date]), type text),
InsertDay = Table.AddColumn(InsertMonth, “DayOfMonth”, each Date.Day([Date])),
InsertDayInt = Table.AddColumn(InsertDay, “DateInt”, each [Year] * 10000 + [MonthOfYear] * 100 + [DayOfMonth]),
InsertMonthName = Table.AddColumn(InsertDayInt, “MonthName”, each Date.ToText([Date], “MMMM”), type text),
InsertCalendarMonth = Table.AddColumn(InsertMonthName, “MonthInCalendar”, each (try(Text.Range([MonthName],0,3)) otherwise [MonthName]) & " " & Number.ToText([Year])),
InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, “QuarterInCalendar”, each “Q” & Number.ToText([QuarterOfYear]) & " " & Number.ToText([Year])),
InsertDayWeek = Table.AddColumn(InsertCalendarQtr, “DayInWeek”, each Date.DayOfWeek([Date])),
InsertDayName = Table.AddColumn(InsertDayWeek, “DayOfWeekName”, each Date.ToText([Date], “dddd”), type text),
InsertWeekEnding = Table.AddColumn(InsertDayName, “WeekEnding”, each Date.EndOfWeek([Date]), type date),
InsertWeekNumber= Table.AddColumn(InsertWeekEnding, “Week Number”, each Date.WeekOfYear([Date])),
InsertMonthnYear = Table.AddColumn(InsertWeekNumber,“MonthnYear”, each [Year] * 10000 + [MonthOfYear] * 100),
InsertQuarternYear = Table.AddColumn(InsertMonthnYear,“QuarternYear”, each [Year] * 10000 + [QuarterOfYear] * 100),
ChangedType1 = Table.TransformColumnTypes(InsertQuarternYear,{{“QuarternYear”, Int64.Type},{“Week Number”, Int64.Type},{“Year”, type text},{“MonthnYear”, Int64.Type}, {“DateInt”, Int64.Type}, {“DayOfMonth”, Int64.Type}, {“MonthOfYear”, Int64.Type}, {“QuarterOfYear”, Int64.Type}, {“MonthInCalendar”, type text}, {“QuarterInCalendar”, type text}, {“DayInWeek”, Int64.Type}}),
InsertShortYear = Table.AddColumn(ChangedType1, “ShortYear”, each Text.End(Text.From([Year]), 2), type text),
AddFY = Table.AddColumn(InsertShortYear, “FY”, each “FY”&(if [MonthOfYear]>=FYStartMonth then Text.From(Number.From([ShortYear])+1) else [ShortYear]))
in
AddFY

2

@atin,

Perfect timing. Earlier tonight, the following excellent video by @Melissa was posted, explaining in detail exactly how to do this:

Enjoy.

  • Brian
3

Hi @BrianJ - Many thanks for your prompt response
Unfortunately the video does not address my present query. However it is very useful for future reports that I would be creating.

At the moment, I already have a Power BI report that I have created using the date table mentioned below. The only downside is having to adjust this date table daily otherwise I get skewed visualisations.

For example. I get daily data on Sales and I want the date table to mirror the current dates, and not show future dates with zero numbers.

I know there is a DateTime.FixedLocalNow() that can be inserted into the date table - however I do not know how this works.

4

@atin,

Quick fix - just invoke the extended date table code with any end date entered when you first invoke it. Then go to the source step and enter the highlighted code below in the second (end date) parameter slot.

  • Brian

image
image1920Ă—813 52.8 KB

5

Hi @BrianJ & @Melissa

I have an error message - please see screen print

IMG_7555 3
IMG_7555 33844Ă—1101 728 KB

Any ideas on solution. Many thanks

6

You are using a different Date table function so omit this part:
, null

and you should be good to go, let me know if that solves it for you.

1 Like
7

@atin,

Rather than using the standard eDNA date table, I would strongly recommend switching over to the eDNA extended date table. This has all the capabilities of the original, plus an array of enhancements that make time intelligence analysis much easier.

Here’s the link to the extended date table:

And here’s a link to an ongoing video series that explains how to take advantage of the expanded capabilities:

  • Brian
8

Many thanks @Melissa

It works now :slight_smile:

9

Hi @BrianJ

Many thanks for the recommendations & your assistance. I would bear this in mind for future reports

Best wishes