topic Re: The first day of the month a few month ago in DAX Commands and Tips

The first day of the month a few month ago

Anonymous — Sun, 07 May 2023 07:46:13 GMT

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] > ??????)

Re: The first day of the month a few month ago

FreemanZ — Sun, 07 May 2023 08:34:41 GMT

hi Anonymous

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

Re: The first day of the month a few month ago

Anonymous — Sun, 07 May 2023 08:54:28 GMT

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 |

Re: The first day of the month a few month ago

FreemanZ — Sun, 07 May 2023 10:09:43 GMT

hi Anonymous

try like:

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

Re: The first day of the month a few month ago

Anonymous — Sun, 07 May 2023 10:13:42 GMT

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.

Re: The first day of the month a few month ago

FreemanZ — Sun, 07 May 2023 10:40:21 GMT

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:

Re: The first day of the month a few month ago

Anonymous — Wed, 10 May 2023 06:48:46 GMT

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.