Forum Discussion

imannsv11's avatar
imannsv11
Regular Visitor
2 years ago
Solved

Combine 2 Date Columns from different Tables

Hi guys,

so I have a Date Slicer on my Dashboard. Where I´m using the "Goods receipt date" Data from a table called SCR.

I also added a new Date Column which is a join from a different Table (ComplaintTable) called "CreatedOn" to the SCR Table.

A little background to my Table:

SCR Table
[ComplaintTable_ComplaintNumber]
[Goods receipt Date] 01.01.2023 - Today
[ComplaintTable_CreatedOn] 01.01.2023 - Today
[KEYX]

ComplaintTable
[CreatedOn] 01.01.2009 - Today
[ComplaintNumber]
[KEYX]

So my problem is, that I have to use the SCR Table for my slicer, but some Complaints are before 01.23 and I want to Include them. So I need like my GoodsReceiptDate + CreatedOn 3 Years before last GoodsReceiptDate. 

I started adding a new table with a regular Calendar starting from 01.01.2020 and added following data:

 

Maybe some of you guys, know how to fix that.


Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    2 years ago

    Hi,

    To start with, you should create a Calendar Table.  Create a relationship (Many to One and Single) from the Date column of both Fact Tables to the Date column of the Calendar Table.  Create calculated column formulas for Year, Month name and Month number.  Sort the Month name column by the Month number.  To any visual, drag Year/Month name/Date from the Calendar Table. 

4 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Icon for Super User rankSuper User
    2 years ago

    could you pls provide some sample data of two tables(not the screenshot) and the expected output?

  • imannsv11's avatar
    imannsv11
    Regular Visitor
    2 years ago

    So, I count the rows of SCR[ComplaintTable.ComplaintNumber] and if SCR[ComplaintTable.ClosedOn] is not null, I mark them as Complaints on my Dashboard. However, some complaints were opened about two years ago and do not count in my data because the GoodsReceiptDate only starts counting at the beginning of 2023. I want to include these additional complaints that were opened two years ago. Therefore, I want to include them in my data when I move it on my Date Slicer.

    SCR

    ItemID

    4504066908_20_1    		OrderNr

    4504066908    		Pos

    20    		SupplierNr

    5101113    		OrderDate

    10.05.2022    		GoodsReceiptDate

    01.03.2023    		KEYX

    4504066908_20    		ComplaintTable.ComplaintNumber

    null    		ComplaintTable.CreatedOn

    null    		ComplaintTable.ClosedOn

    null
    4504055832_22_1450405583222510111320.04.202201.06.20234504055832_22nullnullnull
    4503993561_10_1450399356110510111312.01.202201.08.20234503993561_10nullnullnull
    4504164011_10_1450416401110510111301.11.202201.08.20234504164011_1050020005321107.08.202313.10.2023
    4504037368_10_1450403736810510111328.03.202201.08.20234504037368_10nullnullnull
    4504037368_20_1450403736820510111328.03.202201.08.20234504037368_20nullnullnull
    4504037368_30_1450403736830510111328.03.202201.08.20234504037368_30nullnullnull
    4504037369_10_1450403736910510111328.03.202201.08.20234504037369_10nullnullnull
    4504037369_20_1450403736920510111328.03.202201.08.20234504037369_20nullnullnull
    4504037369_30_1450403736930510111328.03.202201.08.20234504037369_30nullnullnull
    4504071238_100_14504071238100524059223.05.202202.01.20234504071238_10050020005408513.12.202329.01.2024
    4504071238_100_14504071238100524059223.05.202202.01.20234504071238_10050020005377006.11.202305.12.2023
    4504040309_50_1450404030950560017731.03.202202.01.20234504040309_50nullnullnull
    4504042218_20_1450404221820524059204.04.202202.01.20234504042218_20nullnullnull
    4504050067_110_14504050067110524229712.04.202202.01.20234504050067_110nullnullnull
    4504050705_10_1450405070510524059211.04.202202.01.20234504050705_10nullnullnull



    ComplaintTable

    ComplaintNumber

    500200003563    		CreatedIb

    12.01.2009    		EinkBeleg

    null    		Pos

    null    		Supplier

    null    		ClosedOn

    12.01.2009    		KEYX

    null
    50020000363229.01.2009nullnullnull16.03.2009null
    50020000382202.04.2009450093856010524229730.04.20094500938560_10
    50020000382302.04.2009450093856010524229730.04.20094500938560_10
    50020000382102.04.2009450093856010524229730.04.20094500938560_10
    50020000381707.04.2009450093860120532968026.05.20094500938601_20
    50020000383114.04.20094500972967null520671508.05.2009null
    50020000383516.04.2009450093909410529339302.06.20094500939094_10
    50020000383316.04.2009450093909420529339302.06.20094500939094_20
    50020000386227.04.2009450093826310521326024.06.20094500938263_10
    50020000386805.05.2009450093826610521326028.08.20094500938266_10
    50020000386905.05.20094500937870110520688002.06.20094500937870_110
    50020000386605.05.20094500939024140524229723.07.20094500939024_140
    50020000387006.05.2009450093807550525026114.05.20094500938075_50
    50020000387708.05.2009450095273920560005226.06.20094500952739_20
    50020000387808.05.2009450098097010521986808.07.20094500980970_10
    50020000387912.05.2009450097955110560587723.07.20094500979551_10
    50020000391113.05.20094500938045110530579910.06.20094500938045_110
    50020000391313.05.200945009380451305305799null4500938045_130
    • Ashish_Mathur's avatar
      Ashish_Mathur
      Icon for Super User rankSuper User
      2 years ago

      Hi,

      To start with, you should create a Calendar Table.  Create a relationship (Many to One and Single) from the Date column of both Fact Tables to the Date column of the Calendar Table.  Create calculated column formulas for Year, Month name and Month number.  Sort the Month name column by the Month number.  To any visual, drag Year/Month name/Date from the Calendar Table. 

    • ryan_mayu's avatar
      ryan_mayu
      Icon for Super User rankSuper User
      2 years ago

      you can do that to make a flag or do something in DAX. Then what's the expected output based on the sample data you provided?