Date Formula Support Needed
Results 1 to 5 of 5

Thread: Date Formula Support Needed
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jul 2010
    Posts
    54
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Date Formula Support Needed

    Good afternoon all,
    I'm looking for some help with a BI Power Query formula (Trying to cut out lots of manual work) that will take a date from a column (So 10/07/2019) and return back whether that date was Last Week, Last Week -1, Last Week -2, etc up to Last Week -4 in a new column to the right of the date

    Any help or support would be incredible


  2. #2
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,587
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: Date Formula Support Needed

    what does that mean: Last Week -1? Last Week - one day? - one week?

    anyway try:

    Code:
    = if Date.IsInPreviousNWeeks(Date.AddDays([Date],0),1) = true then 1 else if Date.IsInPreviousNWeeks(Date.AddDays([Date],7),1) = true then 2 else if Date.IsInPreviousNWeeks(Date.AddDays([Date],14),1) = true then 3 else if Date.IsInPreviousNWeeks(Date.AddDays([Date],21),1) = true then 4 else null
    it will give you a week number if date is in previous week (1), previous previous week (2), previous previous previous week (3) or previous previous previous previous week (4) else null

    Date Date NthWeek
    10/07/2019
    10/07/2019
    1
    02/07/2019
    02/07/2019
    2
    25/06/2019
    25/06/2019
    3
    18/06/2019
    18/06/2019
    4
    10/06/2019
    10/06/2019
    18/07/2019
    18/07/2019
    03/07/2019
    03/07/2019
    2
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  3. #3
    Board Regular
    Join Date
    Jul 2010
    Posts
    54
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Date Formula Support Needed

    Hi mate,

    This is brilliant thank you. All I meant was any dates from the previous week I want to be able to show LW, any dates from the week before that date would be LW-1 (Last week -1) and so on

    I have loads of sales data by day but want to show some graphs to compare weekly data and in Excel i use LW, LW-1, LW-2, LW-3, LW-1 instead of lets say Week 36, Week 35, Week 34, Week 33, Week 32

    Cheers
    Steve

  4. #4
    Board Regular
    Join Date
    Jul 2010
    Posts
    54
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Date Formula Support Needed

    So managed to change the 'then 1' to 'then "LW" and it worked a treat!!

    How would the same priniples work if i was just looking at weekend performance? How could i show if a date was lets say between Friday - Sunday lastweek then return a 1, Friday - Sunday the week before would be 2 etc etc?

    Any thoughts?

    Cheers
    Steve

  5. #5
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,587
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: Date Formula Support Needed

    there are more function like:

    • Date.IsInPreviousDay
    • Date.IsInPreviousMonth
    • Date.IsInPreviousNDays
    • Date.IsInPreviousNMonths
    • Date.IsInPreviousNQuarters
    • Date.IsInPreviousNWeeks
    • Date.IsInPreviousNYears
    • Date.IsInPreviousQuarter
    • Date.IsInPreviousWeek
    • Date.IsInPreviousYear

    etc...

    see also: Power Query M Reference
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •