Forum Discussion
Cross Filtering calendar table
Thanks for your comment. I found a much simpler solution. When you add a column to the values bucket, only the records with data in that column appears in the filter list.
Thanks,
Anton
You could try creating a date table that uses your fact table to determine the min and max dates that it should show.
Something like Date=calendar(min(salesdate), max(salesdate))
This should create a date table with contiguous dates between your first and last sale.
Thanks for the reply but I need to use the Calendar table from the data warehouse. Besides my sales table has data till the end of the fiscal year and I want to show dates only till the current month.
CALENDARAUTO() does something similar. It looks at the rest of your model and builds a calendar with every year your data contains. So if your sales are May 2013 thru Sep 2018, CALENDARAUTO() builds a calendar table from 1/1/13 - 12/31/18.
Thanks for the replt dedelman_clng. Due to many reasons I have to use the calendar table from my data warehouse. Calendarauto is not an option for me.
I'll preface by saying I'm not very familiar with the limitations of DirectQuery (which I am assuming this is? You are not actually pulling the calendar table into PowerBI?), so while I'm pretty sure this would work if you had the calendar table locally, no promises in a DirectQuery scenario.
In the model, set up the filter direction between Calendar and your Fact table as "both", and then use the date on the Fact table to populate the slicer.
Again, may not work in your scenario if you have no control over the local PBI model.
