Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Marry 2 tables based on different date structures

I need to pull values from two distinct tables. 

  1. One is a transactional table that lists salesorders by date. It is mapped to my calendar table by the date it was created (e.g. 3/8/2018) 
  2. One is a budget table with values by month. It is mapped to the calendar table using year-mo (e.g. 2018-3)

Measures used are

  1. YTD Msr = CALCULATE(

      SUM(

                  salesorders[NetChangeinMRCc]),

                  FILTER(salesorders,

                              salesorders[CreatedBy] <> "KR"

                              && salesorders[CreatedBy] <> "SH")

                  )

  1. YTD Budget = TOTALYTD(

    Sum(Budget[Budget Monthly]),

      CalendarTable[dDate])

 

My mappings look like this:

 

 

How do I create a visualization using both since they are based on differing date structures?

 

 

 

Thanks in advance!

  • Anonymous's avatar
    Anonymous
    8 years ago

    Solved it!  I added a column to the Budget table with an actual date of the 1st of each month.  Then I was able to map that date field to the date field in the Calendar table and it works beautifully!

     

    Sometime you just have to ask the question out loud before you think of the answer!

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable

    Solved it!  I added a column to the Budget table with an actual date of the 1st of each month.  Then I was able to map that date field to the date field in the Calendar table and it works beautifully!

     

    Sometime you just have to ask the question out loud before you think of the answer!