Using a variable to filter data so that is show data for current month

Anonymous — Mon, 19 Sep 2022 18:55:21 GMT

I have DAX code that currently creates a slicer that allows you to show deals that are expected to close in the next 30 days, 60 days, 90 days, and lifetime. All of these slicers are currently working.

I am wanting to change the DAX code for the variable “Life Time” to show deals that are expected to close in the current month.

Here is a sample of my date table ("CM" is a custom column that will desplay "Current" if the day is in the current month):

Date	Year	MonthNumber	Month	Day	CM
1/28/2012 0:00	2012	1	Jan	28	Other
1/29/2012 0:00	2012	1	Jan	29	Other
1/30/2012 0:00	2012	1	Jan	30	Other
1/31/2012 0:00	2012	1	Jan	31	Other
1/1/2013 0:00	2013	1	Jan	1	Other
1/2/2013 0:00	2013	1	Jan	2	Other
1/3/2013 0:00	2013	1	Jan	3	Other
1/4/2013 0:00	2013	1	Jan	4	Other
1/5/2013 0:00	2013	1	Jan	5	Other

This is the original DAX code and what the output looks like:

DateRangePeriods = Var Next30days = ADDCOLUMNS( CALCULATETABLE(DateTable, DATESBETWEEN(DateTable[Date].[Date],Today(),Today()+30)), "Days Range", "30 days") Var Next60days = ADDCOLUMNS( CALCULATETABLE(DateTable, DATESBETWEEN(DateTable[Date].[Date],Today(),Today()+60)), "Days Range", "60 days") Var Next90days = ADDCOLUMNS( CALCULATETABLE(DateTable, DATESBETWEEN(DateTable[Date].[Date],Today(),Today()+90)), "Days Range", "90 days") Var LifetimeData = ADDCOLUMNS( CALCULATETABLE(DateTable), "Days Range", "LifeTime") Return UNION(Next30days,Next60days,Next90days,LifetimeData)

What it looks like on the slicer:

The first way I tried to change it using DATESBETWEEN with STARTOFMONTH and ENDOFMONTH:

DateRangePeriods = Var Next30days = ADDCOLUMNS( CALCULATETABLE(DateTable, DATESBETWEEN(DateTable[Date].[Date],Today(),Today()+30)), "Days Range", "30 days") Var Next60days = ADDCOLUMNS( CALCULATETABLE(DateTable, DATESBETWEEN(DateTable[Date].[Date],Today(),Today()+60)), "Days Range", "60 days") Var Next90days = ADDCOLUMNS( CALCULATETABLE(DateTable, DATESBETWEEN(DateTable[Date].[Date],Today(),Today()+90)), "Days Range", "90 days") Var CurrentMonth = ADDCOLUMNS( CALCULATETABLE(DateTable, DATESBETWEEN(DateTable[Date].[Date],STARTOFMONTH(DateTable[Date]),ENDOFMONTH(DateTable[Date]))), "Days Range", "Current Month") Return UNION(Next30days,Next60days,Next90days,CurrentMonth)

This does not give me any error but when I click on the Current Month slicer nothing is filtered out.

Here is the second way I tried to do it using a custom column “CM” which will display “Current” if the day is withing the current month:

DateRangePeriods = Var Next30days = ADDCOLUMNS( CALCULATETABLE(DateTable, DATESBETWEEN(DateTable[Date].[Date],Today(),Today()+30)), "Days Range", "30 days") Var Next60days = ADDCOLUMNS( CALCULATETABLE(DateTable, DATESBETWEEN(DateTable[Date].[Date],Today(),Today()+60)), "Days Range", "60 days") Var Next90days = ADDCOLUMNS( CALCULATETABLE(DateTable, DATESBETWEEN(DateTable[Date].[Date],Today(),Today()+90)), "Days Range", "90 days") Var CurrentMonth = ADDCOLUMNS( CALCULATETABLE(DateTable, DateTable[CM] = "Current"), "Days Range", "Curret Month") Return UNION(Next30days,Next60days,Next90days,CurrentMonth)

Which gives me this error:

Re: Using a variable to filter data so that is show data for current month

v-xiaosun-msft — Tue, 20 Sep 2022 08:09:43 GMT

Hi Anonymous ,

According to your description, I created a sample and here is my solution.

Please first check if the custom column you created is correct.

1, Make the following modifications according to your method 1.

VAR CurrentMonth = ADDCOLUMNS ( CALCULATETABLE ( 'Tabelle1', DATESBETWEEN ( 'Tabelle1'[Date].[Date], STARTOFMONTH ( 'Tabelle1'[Date] ), EOMONTH ( TODAY (), 0 ) ) ), "Days Range", "Current Month" ) RETURN UNION ( Next30days, Next60days, Next90days, CurrentMonth )

Put “Days Range” into slicer, and you can get the final output:

2, Make the following modifications according to your method 2.

VAR CurrentMonth = ADDCOLUMNS ( CALCULATETABLE ( 'Tabelle1', 'Tabelle1'[CM] = "Current" ), "Days Range2", "Curret Month" ) RETURN UNION ( Next30days, Next60days, Next90days, CurrentMonth )

Put “Days Range2” into slicer, and you can get the final output:

I attach my sample below for your reference.

Best Regards,
Community Support Team _ xiaosun

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

topic Using a variable to filter data so that is show data for current month in DAX Commands and Tips

Using a variable to filter data so that is show data for current month

Re: Using a variable to filter data so that is show data for current month