to get yesterday date with 2 scenario

[rezaaditia]

Hi All -

i am really new in Power BI, even i can say, i just know power BI for 1 week 🙂

anyway, i want to create "filter validate date" for my references in query editor.

currently i able (found) to get yesterday date, but i want to have scenario where if today is monday then it will get Friday date not sunday,

this is to get yesteday date = 

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

 now if today is monday, then the formula should be = 

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

 how to combine this into 1 formula in query editor? 

Thanks in advance

[AiolosZhao]

Hi @rezaaditia ,

You can use a function called Date.DayOfWeek().

eg.

if Date.DayOfWeek([Date]) = 1 then
adddays(-3)
else adddays(-1)

Please try.

Aiolos Zhao

[rezaaditia]

Hi @AiolosZhao 

i did try, no error in syntax but the result shows error

thanks

[AiolosZhao]

Hi @rezaaditia ,

you need to put your column name into your date.adddays() function,

like :

date.adddays(your_date_column, -3)

please make sure the type of your_date_column is date.

Aiolos Zhao

[rezaaditia]

Hi @AiolosZhao 

thank you for your help so far, your formula given is working, but not fit into my dashboard.

i don't want to put specific columns in formula, i want to use general date. can we create it using DateTime.FixedLocalNow()

I did try, but shows error... can you please suggest

if Date.DayOfWeek([Date]) = 1 then
Date.AddDays(Date.From(DateTime.FixedLocalNow()),-3)
else
Date.AddDays(Date.From(DateTime.FixedLocalNow()),-1)

thanks again

[AiolosZhao · Accepted solution]

Hi @rezaaditia ,

could you please show me the error details?

because it works on my sample.

Aiolos Zhao

[rezaaditia]

Hi @AiolosZhao ,

thanks, its working now 🙂