Calculatetable filter a concatenated column using && for filter rule.

[felipereis50]

Can anyone tell me how I concatenate  the Year and Month of a table so I can make a filter inside CalculateTable?

I have a table with a column with the Full Date Ex: 01/01/2009, 01/02/2009 ..... until 12/31/2010

I need to filter all table but only the yearmonth 200901 and 200911 and 201001 and 201009.

Note: Yes, I could create a column already making the concatenate with year + month, for example 200903. 200910 ......

But I initially thought about doing it straight into the formula.

Result: When a tried, I tought  to make the formula below, putting the && ... but not worked.

Does anyone have a tip for me to use the direct rule in the formula, is it possible?

My formula - Example
I made a calculatetable and put the && to see if I can concatenate, but it doesn't work, it was like this;

= calculatetable ("Sales Table", in this part comes the filter rules)

= calculatetable ("Sales Table",
Year ("Sales Table" [Date]) = 2009 && Format(Month ("Sales Table" [Date]),"00) = 01,
Year ("Sales Table" [Date]) = 2010 && Format(Month ("Sales Table" [Date]),"00) = 03
)

If I don't concatenate, the filter would take Month 03, for example, for the two years 2009 and 2010, but i only want por One Year.

---------------------------------------------------------------------------------------------------------

As I did not get it in the form above, I did it by the traditional rule, which would be:

I created a concatenated column YearMonth Ex: 200903

Then, I created a calculatetable and in the filter I put: Sales Table "[Date]) IN {200901,200911,201003,201009})

Bus, as I said, I want to know how to do the same results without create a column.

[v-yangliu-msft · Accepted solution]

Hi  @felipereis50 ,

Here are the steps you can follow：

1. Create calculated column.

Year_Month = YEAR('Table'[Date])&""&MONTH('Table'[Date])

2. Create calculated table.

Table 2 =
FILTER('Table','Table'[Year_Month] in {"20091","200911","20101","20109"})

3. Result：

The data inside is "20091", "200911", "20101", "20109" data：

You can downloaded PBIX file from here.

If my answer is not what you need, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.