Forum Discussion

adam_mac's avatar
adam_mac
Helper 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

BA_Pete's avatar
BA_Pete
Super User
4 years ago

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's avatar
adam_mac
Helper I
4 years ago

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's avatar
    BA_Pete
    Super User
    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