Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
i_kafali
Helper II
Helper II

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.

i_kafali_2-1645605492985.png

 

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.

i_kafali_1-1645605311734.png

 

Thanks in advance for you help

7 REPLIES 7
amitchandak
Super User
Super User

@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.

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. 

@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)
)

 

@amitchandak 
It worked when i have changed second argument as below

i_kafali_0-1645611612072.png

 

But still having issue with DATESINPERIOD function. Last argument which is interval is not accepting MONTH. Do you have any idea how can i solve it=

i_kafali_2-1645611715439.png

 

 

@i_kafali , First Argument is wrong

 

keep it simple like. Not sure why they are taking year of date , that is not even a text

 

date(2020,01,01)

 

 

Also, try this as a measure , assuming [Volume (Ton)] is  measures 

Rolling 3 = CALCULATE([Volume (Ton)],DATESINPERIOD('Date'[Date],MAX('Date'[Date ]),-3,MONTH))

@amitchandak i solved the problem

I had to make it like Date(2020, 1, 1) instead of  Date(2020,1,1)

 

I dont know why it needs one space between arguments. also solved the DATESINPERIOD formule with leaving one space between last arguments. Interesting..

 

i_kafali_1-1645613479711.png

 

 

i_kafali_0-1645613452329.png

 

Oh no.. now my dates begins from 1899 😞

i_kafali_3-1645612421623.png

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors