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

[mhgottsc]

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: 

[v-xiaosun-msft · Accepted solution]

Hi @mhgottsc ,

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.