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

 

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

4 Replies

  • 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

  • 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