MTD Flag column based on yesterdays date

[adam_mac]

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()) -1
then "MTD"
else null

[Anonymous]

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

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

--Nate

[BA_Pete]

Hi @adam_mac ,

I presume you want this to be a 'Current Month to Date' dimension in your calendar table?

If so, then you will need something like this:

if Date.Month(DateTime.LocalNow()) = Date.Month([date])
  and Date.Year(DateTime.LocalNow()) = Date.Year([date])
  and Date.From(DateTime.LocalNow()) > [date]
then "CMTD"
else null

Note that this won't give you any values today, as today is the first of the month, therefore yesterday and before are not in 'current month'. Tomorrow it will show 1st October as "CMTD". and so on.

Pete

[adam_mac]

Hi @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 · Accepted solution]

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