Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
Proud to be a Datanaut!
You could use this logic for the "if" logic.
each if Date.IsInCurrentYear([Date]then "MTD" else null
--Nate
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
Proud to be a Datanaut!
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?
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
Proud to be a Datanaut!