Forum Discussion
Marry 2 tables based on different date structures
I need to pull values from two distinct tables.
- 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)
- 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
- YTD Msr = CALCULATE(
SUM(
salesorders[NetChangeinMRCc]),
FILTER(salesorders,
salesorders[CreatedBy] <> "KR"
&& salesorders[CreatedBy] <> "SH")
)
- 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!
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
- barbforsmanResolver I
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!