Forum Discussion

i_kafali's avatar
i_kafali
Helper II
4 years ago

Date Table and Time Intelligence Functions not working anymore

Hello

I dont know the reason but i had properly working date table function and time intelligence functions before, but not working anymore. I dont know if its related with latest updates.

 

As you may see in the below screenshot it gives error after Date function at 3rd row.


If i dont touch the dax formula its ok but if i F2 + enter all my report will blow up.

 

Same happens to my DATESINPERIOD formula, As you can see below, last parameter is not accepting MONTH as Interval. which was perfectly working before. If i re enter on this formula, again all my report will be broken.

 

Thanks in advance for you help

Date Table
datesinperiod
Time Intelligence

7 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    i_kafali , Close all power bi instance start again and check.

    Hope the first one is created table

     

    If this does not help
    Can you share  a sample pbix after removing sensitive data.

    • i_kafali's avatar
      i_kafali
      Helper II
      4 years ago

      hi amitchandak 
      to be honest i dont remember either it was created table or blank querry
      I tried but cant share pbix file from here also 3rd party uploading websites is not allowed in my organization. So i dont know how can i share it. 

      • amitchandak's avatar
        amitchandak
        Super User
        4 years ago

        i_kafali , This seem like a strange error. I doubt anyone reported such issue with Feb -2022, latest version.

        Can you try creating this date table in a new file?

        Also just use today as second argument, or make sure parenthesis are closed properly

         

        This is my working script , try this

         

         

         

        Date = var _tab1 = ADDCOLUMNS( CALENDAR(Date(2018,10,1),today()), 
"Month Year", format([Date],"mmm-yyyy")
,"Month Year Sort", year([Date]) *100 + month([Date])
,"Year", year([Date])
, "Start of Year", date(year([date]),1,1)
, "End of Year", date(year([date]),12,31)
, "Start of FY", if(month([Date]) <4, date(year([date])-1,4,1), date(year([date]),4,1))
, "End of FY", if(month([Date]) <4, date(year([date]),3,31), date(year([date])+1,3,31))
, "Start Of Month", EOMONTH([date],-1)+1
, "End Of Month", EOMONTH([date],0) 
, "Start Of Qtr", Var _1 = mod(MONTH([Date]),3) 
var _2 = if(_1 =0 ,3,_1) return EOMONTH([Date],-1*_2)+1
, "End Of Qtr", Var _1 = mod(MONTH([Date]),3) 
var _2 = if(_1 =0 ,_1,3-_1) return EOMONTH([Date],_2)
, "Qtr" , format([date], "YYYY\-\QQ")
, "WeekDay" , WEEKDAY([Date],2)
, "Week Start Date", [Date] - WEEKDAY([Date],2) +1
, "Week End Date", [Date] - WEEKDAY([Date],2) +7
, "Weekday Name", FORMAT([date], "ddd")
, "Year Week", Year([Date])*100 + WEEKNUM([Date],2)
)
return  ADDCOLUMNS(_tab1, 
 "FY Qtr" , DATEDIFF( [Start of FY], [Start Of Qtr], QUARTER) +1
, "Week Rank", RANKX(_tab1,[Week Start Date],,ASC,Dense)
)