Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.