Wondering if you guys can help. I have a dim date table with date values that go all the way to December 2018. My fact sales table currently has sales data until end of June 2018.
Every week, I filter my dim date table manually to equal to the last date on the fact sales table and then load the data in the model. Hence, given my fact sales data ends at June 30, 2018, I filter my dim date table in the query view to June 30, 2018 and then load the data.
Is there an automated way (perhaps an M code) that does this functionality automatically? Also, is what I’m doing with filtering the date table proper practice or should my dax code incorporate despite a date table going until Dec 2018 (w/o filtering)?
What I would do in this case is create you date table to go out far into the future, for example 2020.
Then within the date table add a calculated column with logic inside of it that shows up to today (or something like that)
Then you will have a column that you can use in the report or page filter area.
Think of this like an automatic switch across your report that will produce the filters that you need, like you’re having to do manually at the moment.
Here’s an example of the calculated column logic (it’s as simple as this)
Is there any way to do this in the Query Editor using M? Hence, I wouldn’t need to use a report filter and it loads a filtered date table automatically. This would help in instances where I use a FILTER(All(‘Dim-Dates’)) in a calculate function and it override the true/false filter and expose the entire 2020 date list.
Our sales data is currently uploaded on a weekly basis. Hence, using TODAY() may not be ideal in our case. Could you suggest a calculated column formula for the date table that retrieves the last date from the ‘Fact-Sales’ table and then outputs true/false? In relation to the first question, if this can be done in M, that would be helpful.
There likely is a way to create this is M code, BUT I still feel that the calculated column route is a better one, because remember you would always need to refresh your report for that to update.
So say I consumer logs in and see your report that hasn’t been updated all they will see is errors or incorrect data.
So it’s a big one to think about and I’ve seen it happen, that why I’ve always gone done the path of the calculated column.
To get the last day of any sales should be relatively simple.
The difference is in the Source, you create a normal date table then go to the source and use M language.
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.
