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 need to calculate previous ISO weeks with end on Sunday last week and start all the way back in time but exclude year 1900-01-01, where we do not have data.
I have tried different approaches, but many of them return an error, saying, "We cannot apply operator - to types Date and Number"
Would appreciate your help on making this calculation work.
let
_selectedDate = DateTime.LocalNow(),
_endOfPastWeek = Date.EndOfWeek(Date.From(DateTime.LocalNow()), Day.Sunday) - 7, /
_startOfPastWeek = Date.StartOfWeek(_endOfPastWeek, Day.Sunday) - 7,
_dateIsPastPeriod = [Date] <= _endOfPastWeek and [Date] >= #date(1900, 1, 1),
result = if _dateIsPastPeriod then 1 else 0
in
result
Thanx in advance!
Solved! Go to Solution.
Hi @Alisea_MI,
I've just modified your code a bit (to not get error). You can play 🙂
let
Source = Table.FromList(List.Dates(#date(2024,1,1), 366, #duration(1,0,0,0)), Splitter.SplitByNothing(), type table[Date=date]),
selectedDate = Date.From(DateTime.LocalNow()),
endOfPastWeek = Date.AddDays(Date.EndOfWeek(Date.From(DateTime.LocalNow()), Day.Sunday), -7),
startOfPastWeek = Date.AddDays(Date.StartOfWeek(endOfPastWeek, Day.Sunday), -7),
StepBack = Source,
#"Inserted Day Name" = Table.AddColumn(StepBack, "Day Name", each Date.DayOfWeekName([Date], "en-US"), type text),
Ad_DateIsPastPeriod = Table.AddColumn(#"Inserted Day Name", "Date Is Past Period", each [Date] <= endOfPastWeek and [Date] >= #date(1900, 1, 1))
in
Ad_DateIsPastPeriod
Hi @Alisea_MI,
I've just modified your code a bit (to not get error). You can play 🙂
let
Source = Table.FromList(List.Dates(#date(2024,1,1), 366, #duration(1,0,0,0)), Splitter.SplitByNothing(), type table[Date=date]),
selectedDate = Date.From(DateTime.LocalNow()),
endOfPastWeek = Date.AddDays(Date.EndOfWeek(Date.From(DateTime.LocalNow()), Day.Sunday), -7),
startOfPastWeek = Date.AddDays(Date.StartOfWeek(endOfPastWeek, Day.Sunday), -7),
StepBack = Source,
#"Inserted Day Name" = Table.AddColumn(StepBack, "Day Name", each Date.DayOfWeekName([Date], "en-US"), type text),
Ad_DateIsPastPeriod = Table.AddColumn(#"Inserted Day Name", "Date Is Past Period", each [Date] <= endOfPastWeek and [Date] >= #date(1900, 1, 1))
in
Ad_DateIsPastPeriod
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.