Forum Discussion

andyor's avatar
andyor
Icon for Resolver I rankResolver I
4 years ago
Solved

DAX Date Statement not working

Would anybody know why my _last12months and _last24months variables aren't working? They basically filter for nothing

 

I've literally tried every possible combination possible of trying to do this in the same format as the other filters are. All of which work except the last 12 and last 24 months

 

SpecialDates = 
VAR _datetable = DateTable
VAR _today = TODAY()
VAR _month = MONTH(TODAY())
VAR _year = YEAR(TODAY())
VAR _thismonthstart = DATE(_year,_month,1)
VAR _thisyearstart = DATE(_year,1,1)
VAR _lastmonthstart = EDATE(_thismonthstart,-1)
VAR _lastmonthend = _thismonthstart-1  
VAR _thisquarterstart = DATE(YEAR(_today),SWITCH(true,_month>9,10,_month>6,7,_month>3,4,1),1)
VAR _last12months = MONTH(TODAY()-12)
VAR _last24months = MONTH(TODAY()-24)

RETURN UNION(
    ADDCOLUMNS(FILTER(_datetable,[Calendar Date]>=_last12months),"Period","Last 12 Months","Order",1),
    ADDCOLUMNS(FILTER(_datetable,[Calendar Date]>=_last24months),"Period","Last 24 Months","Order",2),
    ADDCOLUMNS(FILTER(_datetable,[Calendar Date]>_today-7),"Period","Last 7 Days","Order",3),
    ADDCOLUMNS(FILTER(_datetable,[Calendar Date]>=_thismonthstart),"Period","This Month","Order",4),
    ADDCOLUMNS(FILTER(_datetable,[Calendar Date]>=_thisquarterstart),"Period","This Quarter","Order",5),
    ADDCOLUMNS(FILTER(_datetable,[Calendar Date]>=_thisyearstart),"Period","This Year","Order",6),
    ADDCOLUMNS(FILTER(_datetable,[Calendar Date]>_today-30),"Period","Last 30 Days","Order",7),
    ADDCOLUMNS(_datetable,"Period","Custom...","Order",8)
)

 

 
  • Solution

     

    VAR _last12months = EDATE(TODAY(),-12)
    VAR _last24months = EDATE(TODAY(),-24)

1 Reply

  • Solution

     

    VAR _last12months = EDATE(TODAY(),-12)
    VAR _last24months = EDATE(TODAY(),-24)