Forum Discussion
Date Table
i have 2 data set 1 set comprises customer data which has 8/9 date columns which rang from 01-JAN-0199 to 31-DEC-9999 both of these ranges are from different date columns, however, 2nd data set contains two date columns ranging from 01-JAN-1976 to 31-DEC-2023. Now i am facing a problem either if i create a date table with CALANDERAUTO() DAX or use a data model from SQLBI (which is quite comprehensive), no matter which / how i create a date table and turn-off Power BI time-intelligence, may time series analysis e.g. accounts opened over years show a blank time plus all account opening date periods (Yearly analysis or any time based analysis). also if i create a visualization from 2nd data set (comprising of transactional activity of each account (debit & credit)) it always shows transactions on blank period plus period selected. I have linked all date columns from both data sets with newly created dates in date tables. kindly resolve this issue.
3 Replies
Hi AteeqSundhu - Create a date table and it is properly configured and linked to your datasets.
Create with dAX function add additional columns for Year, Month, Day, MonthName, Quarter, and YearMonth. correctly link all the date columns in your datasets to the newly created date table.
To exclude blanks and invalid dates, create below DAX code.
ValidDateTransactions =
FILTER (
Transactions,
NOT(ISBLANK(Transactions[TransactionDate]))
&& Transactions[TransactionDate] >= DATE(1976, 1, 1)
&& Transactions[TransactionDate] <= DATE(2023, 12, 31)
)create another measure for to count the number of accounts opened per year:
AccountsOpened =
COUNTROWS (
FILTER (
Customers,
Customers[AccountOpeningDate] >= MIN(DateTable[Date])
&& Customers[AccountOpeningDate] <= MAX(DateTable[Date])
)Hope the logics helps
Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!
)THANKS for the replies from Anonymous which i could not understand and perhaps my data is ok does not need pivoting / unpivoting and rajendraongole1 .
NIL solution corrected / solved my problem. perhaps i coult not explain my self correctly. so below are two tables (EXAMPLES) from differeent files / dataset which i need to link and review with the help of creating optimal / suitable "DATE TABLE"
AC_Open_DT ISSUE_DT EXP_DT FORMATION_DT DATE_OF_BIRTH 12-Jan-2022 16-Jun-2010 30-Dec-2099 1/1/1808 5/12/0199 06-Jan-2021 04-May-2016 06-Jun-9999 06-Dec-2020 07-Jun-1900 04-May-2023 06-Jun-2022 31-Dec-3099 13-Mar-2021 09-Aug-1930 15-Feb-2024 15-Dec-2010 31-Dec-9999 31-Dec-1902 31/8/1710 this is table 1 from 1st data set, RED highlgihted even though are wrong but need to work with these dates.
and
TRN_DT VALUE_DT 01-Jan-2021 31-Dec-2020 02-Jan-2021 01-Jan-2021 03-Jan-2021 02-Jan-2021 04-Jan-2021 03-Jan-2021 05-Jan-2021 04-Jan-2021 06-Jan-2021 05-Jan-2021 07-Jan-2021 06-Jan-2021 08-Jan-2021 07-Jan-2021 . . . . . . 29-Dec-2023 29-Dec-2023 30-Dec-2023 30-Dec-2023 31-Dec-2023 31-Dec-2023 now if i create an DATE TABLE with CALANDERAUTO() then it creates a date table starting from 1/1/0199 till 31-DEC-9999 which is as per data above. but when i link these date columns with "Date" column of newly created CALANDERAUTO() and when i create an analysis that how many accounts openned each date of table 1 it creates a blank row and shows number in count column
---------
BTW rajendraongole1 your 1st solution resulted in error as "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value." & 2nd solution resulted with BLANK no matter how i put it
HI AteeqSundhu,
Perhaps you can try to use unpivot columns feature on your date fields to convert them to attribute(raw field name) and value(date values).
Then you can link this new date value field with your calendar table and use the attribute to accurately control and filter on these records:Unpivot columns - Power Query | Microsoft Learn
Regards,
Xiaoxin Sheng
