Forum Discussion

JaySharma's avatar
JaySharma
Frequent Visitor
2 years ago
Solved

How to make a custom date table for filtering using dates from different tables?

I have 3 tables; let's call them table A B and C. I have dates in those tables: DateA, DateB and DateC. I need to use these as filters using two slicers. one as a dropdown to choose between the name...
Need Help
  • Irwan's avatar
    Irwan
    2 years ago

    hello JaySharma 

     

    please check if this accomodate your need.

     

    1. create new table (fourth table as you mentioned) using UNION to combine all date value

    DateType =
    var _Union = UNION('Date A','Date B','Date C')
    Return
    SELECTCOLUMNS(_Union,"Date",[Date A])

     

    2. create calculated column for Date1

    Date1 = MAXX(FILTER('Date A','Date A'[Date A]='DateType'[Date]),'Date A'[Date A])
     
    3. create calculated column for Date2
    Date2 = MAXX(FILTER('Date B','Date B'[Date B]='DateType'[Date]),'Date B'[Date B])
     
    4. create calculated column for Date3 
    Date3 = MAXX(FILTER('Date C','Date C'[Date C]='DateType'[Date]),'Date C'[Date C])
     
    Result: 

     

    Hope this will help you.

    Thank you.

Ashish_Mathur's avatar
Ashish_Mathur
Super User
2 years ago

Hi,

First and foemost, you should right click on the first column in Power Query and select "Unpivot Other Columns".  Then create a Calendar Table.