Forum Discussion

AteeqSundhu's avatar
AteeqSundhu
Frequent Visitor
2 years ago

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 conta...
Need Help
rajendraongole1's avatar
rajendraongole1
Icon for Super User rankSuper User
2 years ago

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's avatar
    AteeqSundhu
    Frequent Visitor
    2 years ago

    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_DTISSUE_DTEXP_DTFORMATION_DTDATE_OF_BIRTH
    12-Jan-202216-Jun-201030-Dec-20991/1/18085/12/0199
    06-Jan-202104-May-201606-Jun-999906-Dec-202007-Jun-1900
    04-May-202306-Jun-202231-Dec-309913-Mar-202109-Aug-1930
    15-Feb-202415-Dec-201031-Dec-999931-Dec-190231/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_DTVALUE_DT
    01-Jan-202131-Dec-2020
    02-Jan-202101-Jan-2021
    03-Jan-202102-Jan-2021
    04-Jan-202103-Jan-2021
    05-Jan-202104-Jan-2021
    06-Jan-202105-Jan-2021
    07-Jan-202106-Jan-2021
    08-Jan-202107-Jan-2021
    ..
    ..
    ..
    29-Dec-202329-Dec-2023
    30-Dec-202330-Dec-2023
    31-Dec-202331-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