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?
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())
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:
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?
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.