Forum Discussion

Alisea_MI's avatar
Alisea_MI
Icon for Resolver II rankResolver II
2 years ago
Solved

Calculate previous ISO weeks excl year 1900-01-01

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!

Need Help
  • dufoq3's avatar
    dufoq3
    2 years ago

    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

     

1 Reply

  • dufoq3's avatar
    dufoq3
    Icon for Community Champion rankCommunity Champion
    2 years ago

    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