Forum Discussion
Using a variable to filter data so that is show data for current month
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:
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 _ xiaosunIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 Reply
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 _ xiaosunIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.