cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
lbrown Regular Visitor
Regular Visitor

Formula for adding yesterday's date in a new column in Power Query in Excel and Power BI

I need to add a new column that shows yesterday's date. The formula I have to calculate today's date is:

 

Date.From(DateTime.FixedLocalNow())

 

This new column shows me today's date, but I need the column to show yesterday's date. I tried the following combination and I am receiving Error in each cell.

 

Date.From(DateTime.FixedLocalNow()) -1

 

1 ACCEPTED SOLUTION

Accepted Solutions
NickNg278 Senior Member
Senior Member

Re: Formula for adding yesterday's date in a new column in Power Query in Excel and Power BI

@lbrown,

you can use the Date.AddDays() function: 

Date.AddDays(Date.From(DateTime.FixedLocalNow()),-1)

 

View solution in original post

7 REPLIES 7
NickNg278 Senior Member
Senior Member

Re: Formula for adding yesterday's date in a new column in Power Query in Excel and Power BI

@lbrown,

you can use the Date.AddDays() function: 

Date.AddDays(Date.From(DateTime.FixedLocalNow()),-1)

 

View solution in original post

lbrown Regular Visitor
Regular Visitor

Re: Formula for adding yesterday's date in a new column in Power Query in Excel and Power BI

This worked perfectly. Thank you @NickNg278

lbrown Regular Visitor
Regular Visitor

Re: Formula for adding yesterday's date in a new column in Power Query in Excel and Power BI

@NickNg278

 

Update: I added the new column in Power Query Editor and I went back to my dashboard tab to Refresh the Pivot Charts but they are all blank now and I have to rebuild them. Do you know how I can fix this?

Microsoft v-jiascu-msft
Microsoft

Re: Formula for adding yesterday's date in a new column in Power Query in Excel and Power BI

Hi @lbrown,

 

Can you share the file? How did you create the chart? Maybe the old charts use the date "Today" while you change it into "Yesterday".

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
lbrown Regular Visitor
Regular Visitor

Re: Formula for adding yesterday's date in a new column in Power Query in Excel and Power BI

Hi @v-jiascu-msft

 

Unfortunately I'm not able to share the spreadsheet because the data is confidential to my company. I loaded the data table to the Data Model and created the pivot tables based on the workbook's Data Model. The pivot charts were created from the pivot tables. The pivot tables and charts don't currently use a reference to the new data column, but when I added the new column and pressed refresh all they are simply going blank when I refresh.  

Microsoft v-jiascu-msft
Microsoft

Re: Formula for adding yesterday's date in a new column in Power Query in Excel and Power BI

Hi @lbrown,

 

Can you build a dummy one? Sorry, I can't give more suggestions without the data model.

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
lbrown Regular Visitor
Regular Visitor

Re: Formula for adding yesterday's date in a new column in Power Query in Excel and Power BI

Hi Dale,

 

I rebuilt the Excel Sheet in Power BI and I have no problems updating the charts after I add new columns. This error may be an Excel issue, but thank you for your assistance.

 

Best, 

Lauren

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors