Forum Discussion
Date Table
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