Forum Discussion
Transform a static calendar table imported from Excel into a dynamic one
Anonymous,
Okay here is what you can do:
1. Prepare your fact table with Dates, something like I have in my example:2. Retrieve Min and Max (Start and End dates) in numeric format:
3. Create a table (Calendar), where you have Start Date and End Date in two columns:
4. Create new column with all dates between those two dates:
5. Clean the table a bit with removing extra tables:
I can't attach file here, but in case you want - send me PM and I can send you there.
Hi Anonymous ,
I use very simple formula for my Calendar Table. It's a DAX solution but works every time:
Calendar = CALENDAR(MIN('Table'[MyDates]),MAX('Table'[MyDates]))
MyDates is ofcourse a column, which contains dates I want to align with. So depends on my Table and dates, calendar will automaticaly adjust.
Thanks for your reply, Migasuke .
My problem is not that I do not know how to create a dynamic date table in general. The expression that you suggest would work, if only I could apply it.
My problem is that I cannot apply any DAX expression that creates a table at all, because there is already an entire date table. By some turn I need to replace its static [dates] column by a daynamic one. I have to snatch away the static [dates] column, so to say, and put in a dynamic one insted, while retaining all of the column dependencies that I have throughout my data model.
The core of the problem is that my static [dates] column does not come from the Power BI main interface (i.e. the data tab in the left pane), but from the Power Query Editor of Power BI. There it lies in form of a table with one column and is transferred into Power BI. And that is where I currently struggle to replace it by a dynamic one.
Anonymous,
Okay here is what you can do:
1. Prepare your fact table with Dates, something like I have in my example:2. Retrieve Min and Max (Start and End dates) in numeric format:
3. Create a table (Calendar), where you have Start Date and End Date in two columns:
4. Create new column with all dates between those two dates:
5. Clean the table a bit with removing extra tables:
I can't attach file here, but in case you want - send me PM and I can send you there.