Filters or connections Issue

hi  Andvil 

Since there is no [Date] field in 2018, 2019 and 2020, and just there are [Year] and [Month] in them. 

So you could adjust your model as below:

Step1:

Create a YearMonth column in 2018, 2019 and 2020.

Step2:

Summarize CALENDAR TABLE that only contains [Year],[Month],[MonthNameShort],[Quarter],[YearMonth ]

CALENDAR TABLE = 
SUMMARIZE(ADDCOLUMNS (
CALENDAR (DATE(2018,1,1), DATE(2020,12,31)),
"Year", YEAR ( [Date] ),
"MonthNameShort", FORMAT ( [Date], "mmm" ),
"Quarter", "Q" & FORMAT ( [Date], "Q" ),
"Month",SWITCH(MONTH([Date]),
1,"January",
2,"February",
3,"March",
4,"April",
5,"May",
6,"June",
7,"July",
8,"August",
9,"September",
10,"October",
11,"November",
12,"December")),
[Year],[Month],[MonthNameShort],[Quarter],"YearMonth",[Year]&"-"&[Month])

Step3:

Now create relationship by YearMonth column instead of Year column

here is sample pbix file,please try it.

Regards,

Lin

Hi Andvil ,

You need to create a blank query and then add the following code:

= Table.Combine({#"2018", #"2019", #"2020"})

This will create a single table with all 3 datasets then add the following calculated column on the query editor:

Date.EndOfMonth(

#date([Year], (if [Month] = "January" then 1 else
if [Month] = "February" then 2 else
if [Month] = "March" then 3 else
if [Month] = "April" then 4 else
if [Month] = "May" then 5 else
if [Month] = "June" then 6 else
if [Month] = "July" then 7 else
if [Month] = "August" then 8 else
if [Month] = "September" then 9 else
if [Month] = "October" then 10 else
if [Month] = "November" then 11 else
if [Month] = "December" then 12 else
0), 1))

Now go to each of the 3 previous tables rigth click and disable the option Enable load, this will make that tables will not be uploaed to your front end.

Now just make the relationship between the date column and endofmonth column and you are all set.

Check PBIX file attach, be aware that you need to change the souce file location for the 2018, 2019 and 2020 to the correct place since I have made a local file to make this work.

Any questions please tell me.