Linking a date table to a list of months

[OuluChris]

Hi,

I have a "standard" date table that looks like this (it's formatted as a date table):

Date	Year	Month Number	Month	MMM-YY
01/01/2021	2021	1	January	Jan-21
02/01/2021	2021	1	January	Jan-21

And a separate table that looks like this (pilled in through a connection and not formatted as a date table):

Month	Budget Leave
January	0.8
February	0.9
March	1.1
April	1.5
May	1.3
June	1.7
July	2.4
August	3.6
September	2.3
October	1.9
November	1.5
December	6

The values in the second table will be the same every year so they don't need a year adding.

What I want to do is link these two together so if I filter for (say) Jan-21 I will get the result of 0.8. Unfortunately, I can't link them together because the second table 'month' column is formatted as text and it won't seem to accept a date format without adding a year. That is precisely what I'm trying to avoid!

Thanks,

Chris

[v-yingjl]

Hi @OuluChris ,

You can create a one-to-many relationship between two tables based on Month columns and set the cross filter direction to both to get your expected result.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[smpa01 · Accepted solution]

@OuluChris  Does this help?

_TREATAS = 
CALCULATE (
    MAX ( tbl2[Budget Leave] ),
    TREATAS ( VALUES ( 'Date'[Month] ), tbl2[Month] )
)
_CONTAINSROW = 
CALCULATE (
    MAX ( tbl2[Budget Leave] ),
    FILTER('Date',CONTAINSROW(SUMMARIZE(tbl2,tbl2[Month]),'Date'[Month]))
)
_IN = 
CALCULATE (
    MAX ( tbl2[Budget Leave] ),
    FILTER('Date','Date'[Month] IN SUMMARIZE(tbl2,tbl2[Month]))
)
_CONTAINS = 
CALCULATE (
    MAX ( tbl2[Budget Leave] ),
    FILTER('Date',CONTAINS(SUMMARIZE(tbl2,tbl2[Month]),tbl2[Month],'Date'[Month]))
)
_INTERSECT = 
CALCULATE (
    MAX ( tbl2[Budget Leave] ),
    INTERSECT(VALUES(tbl2[Month]),VALUES('Date'[Month])
))

pbix is attached

[smpa01]

@OuluChris  did you have a chance to look into this?

[AlexisOlson]

You may want to clarify that these are several separate solutions. I was a bit confused when I first saw it.

Assuming you have the relationship set up as in your .pbix. Here are a couple more options:

_CROSSFILTER =
CALCULATE (
    MAX ( tbl2[Budget Leave] ),
    CROSSFILTER ( 'Date'[Month], tbl2[Month], BOTH )
)

_TABLEFILTER = 
CALCULATE ( MAX ( tbl2[Budget Leave] ), 'Date' )

You could also set up the relationship as many-to-many with the date table filtering the budget table but that's not best practice.

[smpa01]

@AlexisOlson  Thanks for explaining out that !!!

[wdx223_Daniel]

Try link the month column of table2 to the month column of date table. this will not let you benefit the intellegent of time functions. but you can use RELATED function to get the value of table2, assuming the value of table2 is distinct.