Updating the date table

Power Query
1

Hi.

I use the Enterprise DNA date query for all my reports. However, each day I am manually changing the date script to include days up to today in the Date column - see below. (I tried entering forward dates - those higher than today - but this resulted in my charts showing horizontal lines/flat data past the last data point.)

Is there a way of adding something to the Date query so that it automatically adds today’s date each new day?

Thanks

image

2

Sure but if you’re doing any time intelligence it’s probably best to have whole years of data in your Dates table…

Alternatively you could use the boolean filter IsAfterToday to stop that behaviour. By placing it as a page or report level filter but you can also use it in measures of course. There is one in the Extended Date table, you can find that here.

However if you’re set on having a Date table that runs until today you could use this M code as Enddate for your calendar M function that will update the Enddate each time the query is executed.
Date.From( DateTime.FixedLocalNow())

I hope this is helpful.

3

Thanks again. So much to learn.

4

Hi Melissa

I added the M Code to the date table as below but the date finishes with yesterday’s date.

Just wondering what I did wrong?

image
image760×239 27.9 KB

image

5

Ok so check the Calendar function you are invoking, if you use the eDNA one there should be a DayCount step. Duration counts the days between the given dates so you have to add 1 to include the last day as well - like:

DayCount = Duration.Days(Duration.From(EndDate - StartDate)) +1

6

Hi Melissa

Thank you. Working perfectly now.

I want to learn best practice. In your opinion is it better to have the dates table include the current year (whenever that may start) and to filter the dates of any charts or tables to make them meaningful?

7

Hi Melissa,

Not sure if I should have started a different thread. But I want to be able to change the format of the FY column in the date table. so instead of FY20 , I want it to show 2019-20 , or is there a way I can do that with calculated column.

Thank you

8

Hi @Ikay,

When you have a ‘new question’ please always create a new topic with a suitable title and description. The reason I’m asking is:

  • New questions in closed threads tend to get lost…
  • A suitable title to the question will allow others to find the topic when the have a similar query

I would resolve this in PQ, so create a new thread, provide your date table M code and @mention me, thanks!

1 Like