Forum Discussion

Babbal's avatar
Babbal
New Member
3 years ago
Solved

Merge two tables with different date formats

I have to merge two tables named - Actuals and Target. Actuals tables has date wise data (1st Jan,2nd jan,.....31st jan) whereas target table has month wise month data (jan,feb,so on).

Want to merge/join these tables on the basis of date but if i join it on date basis, target is populating only for 1st jan because target data has only one date i.e., 1st jan but if create a new column start of month and join it on that basis, target gets repeated for all the 31 dates in january. 

Basically i want whenever i filter date wise, the actual nos should change but for that month target should remain constant throughout all the dates.

Please help!

 

 

Need Help
Show and Tell
Tips and Tricks
  • v-xiaosun-msft's avatar
    v-xiaosun-msft
    3 years ago

    Hi Babbal ,

     

    Since that there is only one date in your "TARGET TABLE", if you establish a relationship between two tables, target will be populating only for 1st jan.

    Do not establish a relationship between two tables, and then connect two dates by their year.

    Create two measures in TARGET table to return the year and value.

    Measure = YEAR(MAX('TARGET TABLE'[Date]))
    Measure 2 = MAX('TARGET TABLE'[Target])

    Create a column in ACTUAL table.

    Column =
IF (
    'TARGET TABLE'[Measure] = YEAR ( 'ACTUAL TABLE'[Date] ),
    'TARGET TABLE'[Measure 2]
)

    Then you can get the output.

    I attach my sample below for your reference.

     

    Best Regards,
    Community Support Team _ xiaosun

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • vaibhavkale570's avatar
    vaibhavkale570
    Icon for Resolver III rankResolver III
    3 years ago

    You cannot reverse the granularity from month to date.

    If you still want to do it, first you will have to group the dates in months and then join it.
    or if you are calculating the target based on the actual table you can just include you calculation in new calculated table.

  • v-xiaosun-msft's avatar
    v-xiaosun-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi Babbal ,

     

    Since that there is only one date in your "TARGET TABLE", if you establish a relationship between two tables, target will be populating only for 1st jan.

    Do not establish a relationship between two tables, and then connect two dates by their year.

    Create two measures in TARGET table to return the year and value.

    Measure = YEAR(MAX('TARGET TABLE'[Date]))
    Measure 2 = MAX('TARGET TABLE'[Target])

    Create a column in ACTUAL table.

    Column =
IF (
    'TARGET TABLE'[Measure] = YEAR ( 'ACTUAL TABLE'[Date] ),
    'TARGET TABLE'[Measure 2]
)

    Then you can get the output.

    I attach my sample below for your reference.

     

    Best Regards,
    Community Support Team _ xiaosun

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.