Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Mapping multiple date fields to Calendar Date Table

Hi Power users,

I have a dataset where I have many date fields. Please see below sample data.

NumberBranchCredit_dateApp_DateClose_DateDoc_Date
1000MI11/10/20181/12/20169/8/20179/20/2018
155FL6/8/20183/28/201610/14/201812/28/2016
637CA6/23/20181/2/20189/19/20178/17/2017
688CA1/23/20163/31/20188/29/20166/25/2017
736CA7/30/201812/20/20187/11/20173/2/2016
410PA11/30/20179/9/20178/21/201610/24/2018
323CA2/20/201711/6/201810/7/20174/4/2016
891CA5/10/20177/29/20171/11/201611/14/2016
394CA10/26/20183/19/20182/12/20179/4/2016

Now, I want a date slicer which works on all date fields. I am creating a calendar table and then creating relationship between calendar table and date fields of my dataset. But when I am selecting any Month and Year I am not seeing any data, I don't know how to fix it. Any help please?

My Calendar_table DAX Query: 

Calendar_Table = ADDCOLUMNS(
CALENDAR( DATE( 2015, 1, 1) , DATE(2020, 12, 31) ),
"Day", DAY([Date]),
"Month", MONTH([Date]),
"Year", YEAR([Date])
)

 

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Here's my mapping:

     

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    Hi Anonymous

    I can't reproduce your problem about "when I am selecting any Month and Year I am not seeing any data".

    Could you add your operation about this in my pbix?

     

    Additionally, since there are many date columns in the data table which can have relationships with the calendar date table, but only one date column can have active relationship. the active relationship for my test is "Credit_date".

     

    Best Regards

    Maggie