The first day of the month a few month ago

[Anonymous]

I have  a table with 2 columns: Name, month

The column month contains month from the past 12 months.

I would like to create a new talbe that will take the existing, but will filter only the last 3 month not including the current.

So if today is 5/2023, I would like the new table to contain only the rows with months:

02/2023

03/2023

04/2023

05/2023

I am trying to create it the following way:

Existing table name: fullTable

New table = CALCULATETABLE (fullTable, FILTER(fullTable,  'fullTable'[month] > ??????)

[FreemanZ]

hi @Anonymous 

could you elaborate your case, with sufficient data and expected result directly?

[Anonymous]

In my model I have the following table:

Table: Full Table
|Name|Month|
|--| --- |
|a | 11/1/2022 |
|b |11/1/2022|
|c |11/1/2022 |
|c |1/1/2023 |
|b |2/1/2023|
|b |1/1/2023 |
|a |3/1/2023 |
|c |4/1/2023 |
|a |4/1/2023 |
|a |5/1/2023 |
|b |4/1/2023 |

I need to create from this table a filterred table which will look like this:

Table: New Table
|Name|Month|
|--| --- |
|b |2/1/2023|
|a |3/1/2023 |
|c |4/1/2023 |
|a |4/1/2023 |
|a |5/1/2023 |
|b |4/1/2023 |

[Anonymous · Accepted solution]

Hi @Anonymous ,

According to your statement, I know you want to create a calculated table which will show 3 last months data to current dynamiclly.

New Table =
CALCULATETABLE (
    'Full Table',
    FILTER ( 'Full Table', 'Full Table'[Month] >= EOMONTH ( TODAY (), -4 ) + 1 )
)

Result is as below.

 

Best Regards,
Rico Zhou

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

[FreemanZ]

hi @Anonymous 

try like:

New table =
CALCULATETABLE (
fullTable,
fullTable[month] >=DATE(2023,2,1)
)

[Anonymous]

Thanks, however, I need it to be dynamic. So if today we are in May, I need the  new table to be filterred from 2/23. In June it will need to be filterred from 3/23, in 5/24 fron 2/24 etc.

[FreemanZ]

hi @Anonymous 

not sure if i fully get you, you may try to

1) plot a slicer with a new isolated table. You may create one like:

Slicer = ALL(fulltable[Month])

2) plot a table visual with necessary columns, but filtered with a measure like:

measure = 
IF(
    MAX(fulltable[Month])<SELECTEDVALUE(Slicer[Month])
        &&MAX(fulltable[Month])>=EDATE(SELECTEDVALUE(Slicer[Month]),-3),
    1,0
)

Note: choose 1 for the meaure.

it worked like: