Forum Discussion
adam_mac
Helper I
Helper IMTD Flag column based on yesterdays date
Hi, i am trying to create a MTD column based on yesterdays date but below query keeps returning an error. Any ideas what im doing wrong? if Date.Day([Date]) <= Date.From(DateTime.FixedLocalNow())...
Ah, ok.
So today, you would want all of September to still be "MTD"?
In that case, try this:
if Date.Month(Date.AddDays(DateTime.LocalNow(), -1)) = Date.Month([date]) and Date.Year(Date.AddDays(DateTime.LocalNow(), -1)) = Date.Year([date]) and Date.From(Date.AddDays(DateTime.LocalNow(), -1)) >= [date] then "MTD" else nullIf you want it a bit neater, this is the same just using a variable for yesterday's date:
let Date.Yest = Date.AddDays(DateTime.LocalNow(), -1) in if Date.Month(Date.Yest) = Date.Month([date]) and Date.Year(Date.Yest) = Date.Year([date]) and Date.From(Date.Yest) >= [date] then "MTD" else nullPete
adam_macHelper I
Helper IHi BA_Pete , unfortunately my data is always a date behind. So i would need MTD to not include today (which is 01/10/2021) and to consider yesterday (30/09/2021) as the "today". Is this possible?
BA_Pete
Super User
Super UserAh, ok.
So today, you would want all of September to still be "MTD"?
In that case, try this:
if Date.Month(Date.AddDays(DateTime.LocalNow(), -1)) = Date.Month([date])
and Date.Year(Date.AddDays(DateTime.LocalNow(), -1)) = Date.Year([date])
and Date.From(Date.AddDays(DateTime.LocalNow(), -1)) >= [date]
then "MTD"
else null
If you want it a bit neater, this is the same just using a variable for yesterday's date:
let
Date.Yest = Date.AddDays(DateTime.LocalNow(), -1)
in
if Date.Month(Date.Yest) = Date.Month([date])
and Date.Year(Date.Yest) = Date.Year([date])
and Date.From(Date.Yest) >= [date]
then "MTD"
else nullPete