Is there a way to make power BI 'think' that today is a different date?

[thegusman]

I would love for my file to think it is yesterday 12/31, instead of today 1/1.  I have a ton of calculated columns created in Power Query with various dates through out last year.  I really would prefer to have to go back and recalc the many columns. 

Does anyone have any ideas that might help me in this situation? 

[v-deddai1-msft · Accepted solution]

Hi @thegusman ,

You can use DateTime.Date(#date(2020, 12, 31)) to replace DateTime.LocalNow()). DateTime.LocalNow()) get the date from your server, and you can also change the date of your server which hold the pbix file.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[Anonymous]

Hey, can u explain a little on what are you using for these date operations. 
i am assuming u would have created a rolling calender aka date table. 

if so, you can try modifying that date column with today()-2, as which will give 31st Dec 2020 when we are standing on 2nd Jan 2021.

[thegusman]

I used "if then" statements in custom columns to flag the data in Power Query i.e. 1 or 0. 

Now that it is the new year, and I want to view all of 2020, the custom date columns (listed below) have shifted with today's date in the new year.  

Instead of using *DateTime.LocalNow* in the expressions below, can I use something to show it as 12/31/2020?

Here are some of the significant date columns that I created. 

-Date.EndOfMonth(Date.AddMonths(DateTime.Date(DateTime.LocalNow()),-1)))
-Date.EndOfMonth(Date.AddMonths(DateTime.Date(DateTime.LocalNow()),-2))),

-Date.EndOfMonth(Date.AddMonths(DateTime.Date(DateTime.LocalNow()),-13))),
-Date.EndOfYear(Date.AddYears(DateTime.Date(DateTime.LocalNow()),-1))),

-Date.EndOfYear(Date.AddYears(DateTime.Date(DateTime.LocalNow()),-2))),
-Date.EndOfYear(Date.AddYears(DateTime.Date(DateTime.LocalNow()),-3)))
-Date.EndOfYear(Date.AddYears(DateTime.Date(DateTime.LocalNow()),-4)))

[amitchandak]

@thegusman , what exactly you mean by this , what are you trying achieve

[thegusman]

I need my pbix file to think that it is 12/31/2020, and not 2021.  The data I'm looking at is date sensitive.   Instead of going back and changing the dates to account for the new year, I would like to make power bi think it is still 2020 and show me, for example, YTD data as if it were still 12/31/2020.  

[amitchandak]

@thegusman , if you are not using a date filter. The best is that you stop your calendar on 2020-12-31 .

refer to this video, how time intelligence takes dates - https://www.youtube.com/watch?v=OBf0rjpp5Hw&list=LL&index=3

[Paulyeo11]

I never try it , may be you can set your note book calendar date to 12/31/2020.

[thegusman]

I just tried your suggestion, sadly that did not work. I even did a reboot. It must not rely on the machines date/time. 

[Paulyeo11]

that is why since i started PBI , i alway focus on create expression that don't need maintenance.