cancel
Showing results for 
Search instead for 
Did you mean: 
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.



Learn Power BI - Full Course with Dec-2022, with new DAX functions like Window, Index, Offset !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Formatted Profit and Loss Statement with empty lines

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

 



Learn Power BI - Full Course with Dec-2022, with new DAX functions like Window, Index, Offset !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Formatted Profit and Loss Statement with empty lines

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



Learn Power BI - Full Course with Dec-2022, with new DAX functions like Window, Index, Offset !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Formatted Profit and Loss Statement with empty lines

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

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors