Date Table

[AteeqSundhu]

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. 

[Anonymous]

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

[rajendraongole1]

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!!

)

[AteeqSundhu]

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