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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
learner03
Post Partisan
Post Partisan

Today's date not refreshing in Power Bi Service

I created a dashboard yesterday and published in Power BI serice. I scheduled refresh but when it refreshed today morning, the calendar table didn't refreshed and the measures where I was using "TODAY()"are showing BLANK. How can I correct this? It seems that the calander table is not refreshing. In below picture, it is showing 6 july, but it should show 7th july after refresh.

akapoor03_0-1625618863910.png

akapoor03_1-1625618899068.png

 

 

1 ACCEPTED SOLUTION
aj1973
Community Champion
Community Champion

Ok then the TODAY() in your formula is good. did you replace 1 by 2 or 3 or... in the M code?

let
Source = List.Dates(StartDate, Length+2, #duration(1, 0, 0, 0)),

...

 

doing so, you should be good.

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

View solution in original post

16 REPLIES 16
prudvit
Frequent Visitor

Simple and Temporary Fix:
add a Filter and say in the the next x days in relative Date.

aj1973
Community Champion
Community Champion

Hi @learner03 

how did you create the calendar table?

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

@aj1973 Using the below code-

 

let
Source = List.Dates(StartDate, Length+1, #duration(1, 0, 0, 0)),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
StartDate = #date(2021, 1, 1),
Today = DateTime.Date(DateTime.LocalNow()),
Length = Duration.Days(Today - StartDate),
Custom1 = #"Changed Type",
#"Inserted Year" = Table.AddColumn(Custom1, "Fin Year", each Date.Year([Date]+#duration(184,0,0,0)), Int64.Type),
#"Inserted Month Name" = Table.AddColumn(#"Inserted Year", "Month Name", each Date.MonthName([Date]), type text),
#"Inserted Day Name" = Table.AddColumn(#"Inserted Month Name", "Day Name", each Date.DayOfWeekName([Date]), type text),
#"Inserted Month" = Table.AddColumn(#"Inserted Day Name", "Fin Month", each if Date.Month([Date]) >=7 then Date.Month([Date])-6 else Date.Month([Date])+6 , Int64.Type),
#"Inserted Day of Week" = Table.AddColumn(#"Inserted Month", "Day of Week", each Date.DayOfWeek(([Date]), Day.Sunday), Int64.Type),
#"Inserted First Characters" = Table.AddColumn(#"Inserted Day of Week", "MMM", each Text.Start([Month Name], 3), type text),
#"Inserted First Characters1" = Table.AddColumn(#"Inserted First Characters", "DDD", each Text.Start([Day Name], 3), type text),
#"Reordered Columns" = Table.ReorderColumns(#"Inserted First Characters1",{"Date", "Fin Year", "Month Name", "MMM", "Fin Month", "Day Name", "DDD", "Day of Week"}),
#"Added Custom" = Table.AddColumn(#"Reordered Columns", "FYMM", each ([Fin Year]-2000)*100 + [Fin Month]),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"FYMM", Int64.Type}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type1", "MonthID", each (Date.Year([Date]) - Date.Year(StartDate))*12 + Date.Month([Date])),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom1",{{"MonthID", Int64.Type}})
in
#"Changed Type2"

 

Also I don't see the calendar table under Data source credentials (Don't know if I am suppose to see this underthis or not)-

akapoor03_0-1625623718224.png

This is the first dashboard I have published and I am new to this.

aj1973
Community Champion
Community Champion

It's all good

aj1973_0-1625623832807.png

where is your location?, check the browser you need to change the date/time of your browser

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

@aj1973 

I am in Australia. So how and where should I change the date? And do i need to make changes in the formula too where I am using Today()? for example:

Current Closed Orders =
CALCULATE (
COUNT( 'Packing Jobs'[JobID] ),
FILTER ( ALLSELECTED('Packing Jobs'), [Job Close Time(Date format)] =TODAY() )
)
 
aj1973
Community Champion
Community Champion

There is different items where you need to change regional settings, like the browser, the desktop, the M code..

can you please add a measure in you desktop Today = today(), add it in a card and then show me the card?

also in your M code for your Calendar date replace 1 by 2 and you will get the 7/7/2021 date

...

aj1973_0-1625626525389.png

 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

I am getting this in the card visual-

akapoor03_0-1625629438453.png

 

This is without changing anything. 

 

 

aj1973
Community Champion
Community Champion

Ok then the TODAY() in your formula is good. did you replace 1 by 2 or 3 or... in the M code?

let
Source = List.Dates(StartDate, Length+2, #duration(1, 0, 0, 0)),

...

 

doing so, you should be good.

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

Also, I have created these 2 othercards with NOW and UTCNOW, if this is helpful. Does the POWER BI service refresh takes place based on UTC time?

akapoor03_0-1625630257688.png

 

aj1973
Community Champion
Community Champion

You are not using these times any way.

You still didn't answer my question and it's getting late here in Canada.

To answer your question, Power Bi service refreshes your report through a Gateway and the Gateway follows a schedule. So I must understand that you report is not refreshing in the service and that's why your cards are showing Blanks....

Please verify I have mentioned to you.

 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

Sorry about thtat.

Yes, I replaced +1 in the M code with +2 andthe calender is now showing-

akapoor03_0-1625631110944.png

The card visual is good and still showing 7/7/2021.

Just last question, does this mean I still have to change time in   browser and the desktop etc.

 

Thanks

aj1973
Community Champion
Community Champion

Your Desktop and system is ok according to the measure TODAY you put in the card. The browser i am not sure, check it out.

However what I am suspecting is that you are not using a Gateway to refresh you report. Did you install a Gateway? Does the refresh in your desktop give you the correct data into your visuals?

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

So when I refreshed desktop in morning, then only calander populated today's date and I got correct result in Desktop. 

I installed gateway and below is the screenshot-

akapoor03_0-1625631793643.png

In the morning, the scheduled refresh was successfully complted but the only problem was that, it did not show Today's result and the calander did not populated today's date and was showing 6th july only instead of 7th July.

 

aj1973
Community Champion
Community Champion

Well after you corrected the M Code, republish the report to the service and manually refresh it. The visuals should be corrected as well.

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

Sure, will do.

 

Thank you so much for your time and effort.

 

Thanks again

aj1973
Community Champion
Community Champion

Yeah it's about time to go to bed.

 

You welcome

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.