Forum Discussion

savanahubio's avatar
savanahubio
Frequent Visitor
8 years ago
Solved

Relationship between 2 tables

Hello,

 

I am trying to look at Sales versus Sales Forecast. My sales Table is daily data by item. 

My Forecast table is by week and item. 

 

How can I create a relationship so I can look at this without the Forecast showing up as a total sum?

 

Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    8 years ago

    Hi savanahubio,

     

    Based on my test, you should be able to use the formula below to create a new measure to calculate the forecast value, then show the created measure as Line Values on the chart to get the expected result without creating any relationships in your scenario.

    Measure = 
CALCULATE (
    SUM ( 'POS Forecast'[POS Forecast] ),
    FILTER (
        'POS Forecast',
        CONTAINS (
            'Daily Sales by Item',
            'Daily Sales by Item'[Item Description], 'POS Forecast'[Item Description]
        )
            && 'POS Forecast'[Year] = MAX ( 'Daily Sales by Item'[Year] )
            && 'POS Forecast'[Week Num] = MAX ( 'Daily Sales by Item'[Week Num] )
    )
)

     

    Here is the modified pbix file for your reference. :smileyhappy:

     

    Regards

9 Replies

  • parry2k's avatar
    parry2k
    Super User
    8 years ago

    You need to have date dimension in your model and then link your both tables on date with this date dimension table.

     

    Use period from date table on x axis and values from these tables and tht will do the job. There are many articles on how to create date/calendar dimension for time intelligence reporting.

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee
    8 years ago

    Hi savanahubio,

     

    In addition, an alternative solution could be that firstly creating a calculate column in your Sales table to get the corresponding WeeK Starting Date for each row. The formula below is for your reference.

     

    WeekStarting =
VAR weekNum =
    WEEKNUM ( 'Sales'[Date] )
VAR year =
    YEAR ( 'Date'[Date] )
RETURN
    CALCULATE (
        FIRSTDATE ( 'Sales'[Date] ),
        FILTER (
            ALL ( 'Date' ),
            WEEKNUM ( 'Sales'[Date] ) = weekNum
                && YEAR ( 'Sales'[Date] ) = year
        )
    )

     

    Then you should be able to create a relationship between your Sales table and Forecast table with the "WeekStarting" column. :smileyhappy:

     

    Regards

    • savanahubio's avatar
      savanahubio
      Frequent Visitor
      8 years ago

      I did the opposite of this an created the Week Ending in my Forecast table because that is what I go by. Because I have item detail in the Forecast table there are 3 values for any given week. I cannot create a relationship because there arent unique values. I think I need to create a table with just the WeekNum and the Last Day of the week and then make a relationship between both other tables. 

       

      I do not know how to go that but I will try! 

    • savanahubio's avatar
      savanahubio
      Frequent Visitor
      8 years ago

      I did the opposite of this an created the Week Ending in my Forecast table because that is what I go by. Because I have item detail in the Forecast table there are 3 values for any given week. I cannot create a relationship because there arent unique values. I think I need to create a table with just the WeekNum and the Last Day of the week and then make a relationship between both other tables. 

       

      I do not know how to go that but I will try! 

      • v-ljerr-msft's avatar
        v-ljerr-msft
        Microsoft Employee
        8 years ago

        Hi savanahubio,

         

        Could you share a sample pbix file(with just some sample/mock data) which can reproduce the issue, so that we can further assist on it? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading. :smileyhappy:

         

        Regards