Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I have six tables which all contain a "RecordActiveFrom" datetime column. I'm trying to create a calendar table which uses the minimum value of all of the RecordActiveFroms as the startdate and the maximum value of all of the RecordActiveFroms as the end date.
I have tried editing the calendar table code in the advanced editor to take a StartDate that looks similar to "List.Min(Table1[RecordActiveFrom], Table2[RecordActiveFrom], Table3[RecordActiveFrom])" and List.Max for the maximum value but this has returned a number of different errors with date formatting and invalid values.
I have also tried creating a nested MIN function on a calculated column but again ran into some issues.
Does anyone have an efficient solution which could be used for this?
I believe that you should just be able to use the DAX CALENDARAUTO function for this. My understanding of CALENDARAUTO is that it creates a calendar table by looking at the dates in the data model and creates a calendar table based upon those dates.
Hello @Greg_Deckler
Does the calendarauto get bigger if we add new data out of the calendarauto?
For example mine begins in 2018 but I will receive data from 2016 and don't know that will happen.
Thank you very much,
DM
I think this would work but my model includes other datetime columns aside from RecordActiveFrom that I don't want to calculate the minimum or maximum for. I believe CALENDARAUTO would factor in those dates making my date range larger than needed.
The calendar DAX function will work to generate a Dates table if you can get the data into a correct Date format.
Date Table = CALENDAR(min('Billing Data'[Date]),max('Billing Data'[Date]))
I think there in lies your problem. If all of your RecordActiveFrom fields are not valid Date field, then you are going to have a problem.
My approach would be:
1) Ensure that the fields are Date (Or Date/Time) type.
2) Create measures with the minimum and maximum date fields
3) Use the CALENDAR function with the new measures.
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |