Forum Discussion

adam_mac's avatar
adam_mac
Icon for Helper I rankHelper I
4 years ago
Solved

MTD 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())...
Need Help
  • BA_Pete's avatar
    BA_Pete
    4 years ago

    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 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 null

    Pete

Anonymous's avatar
Anonymous
Not applicable
4 years ago

You could use this logic for the "if" logic. 

 

each if Date.IsInCurrentYear([Date]then "MTD" else null

 

--Nate