Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Filter names within matching date range from previous week

Hello,

I have the following Exceptions table in Power BI, loaded in Power Query. It is connected to a calendar table which has week offset column to find previous week easily. I am trying to find the list of names where the range of dates from the start to end dates (inclusive) overlaps with any date from the previous week (from Monday to Sunday). Please note that this should be dynamic and should work as the year changes, since for the last week of the year, it might overlap between two different years. For example, week 52 in 2021 is partly in 2021 and 2022.

Is that possible to do in Power Query? Any help is much appreciated!

The results should be in a new Filtered table as below (previous week is from 04/04 to 10/04):

Student nameReasonStart dateEnd date
Zagrodnosick01/04/202205/04/2022
Luntassick05/04/202208/04/2022
Frei Pauloblank07/04/202214/04/2022
Seedorfblank28/03/202211/04/2022
Gangarampurlate28/03/202220/04/2022
Luntassick01/04/202210/04/2022
Zagrodnoblank07/04/202209/04/2022
Gangarampursick04/04/202204/04/2022

Here is the download link to my PBIX file (on the Google Drive page, click the download icon at the top-right corner of the window): https://drive.google.com/file/d/1gEO4QCxNVW72CL_XEEif8ynd3zqO-D6-/view?usp=sharing

Need Help
  • v-kkf-msft's avatar
    v-kkf-msft
    4 years ago

    Hi Anonymous ,

     

    Please create a blank query and paste the M code.

     

    let
    Source = Exceptions,
    #"Added Custom" = Table.AddColumn(Source, "StartToEnd", each List.Dates([Start date], Duration.Days(Duration.From( [End date] - [Start date] ))+1,#duration(1, 0, 0, 0))),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "PreviousWeek", each Table.SelectRows(Calendar, each Date.IsInPreviousWeek([Date])=true)[Date]),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom", each List.Count( List.Intersect({[StartToEnd],[PreviousWeek]}))),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom2", each [Custom] > 0),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"StartToEnd", "PreviousWeek", "Custom"})
in
    #"Removed Columns"

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
    Best Regards,
    Winniz
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

5 Replies

  • v-kkf-msft's avatar
    v-kkf-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi Anonymous ,

     

    Please create a blank query and paste the M code.

     

    let
    Source = Exceptions,
    #"Added Custom" = Table.AddColumn(Source, "StartToEnd", each List.Dates([Start date], Duration.Days(Duration.From( [End date] - [Start date] ))+1,#duration(1, 0, 0, 0))),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "PreviousWeek", each Table.SelectRows(Calendar, each Date.IsInPreviousWeek([Date])=true)[Date]),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom", each List.Count( List.Intersect({[StartToEnd],[PreviousWeek]}))),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom2", each [Custom] > 0),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"StartToEnd", "PreviousWeek", "Custom"})
in
    #"Removed Columns"

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
    Best Regards,
    Winniz
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi v-kkf-msft Thanks for your reply! But the results from your solution are not correct. I have included the expected results at the end of my first post.

      • v-kkf-msft's avatar
        v-kkf-msft
        Icon for Community Support rankCommunity Support
        4 years ago

        Hi Anonymous ,

         

        Yes, because yesterday was Monday (2022-4-18), so previous week was 2022-4-11 to 2022-4-17. So the calculation is different from what you posted (from 04/04 to 10/04).

         

         

        If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
        Best Regards,
        Winniz
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

         

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    Anonymous , If you need based on Today

     

    Last Week today =
    var _st = today() +-1*WEEKDAY(today(),2)+1 -7
    var _end =today()+ 7-1*WEEKDAY(today(),2) -7
    return
    CALCULATE(Count(Table[Start date]),filter('Table','Table'[Start date]<= _end && 'Table'[End Date]>=_St ))

     

     

    Prefer a date table for selection

     

    Last Week based on selected date =

    var _today= maxx(allselected('Date'), 'Date'[Date])
    var _st = today() +-1*WEEKDAY(_today,2)+1 -7
    var _end =today()+ 7-1*WEEKDAY(_today,2) -7
    return
    CALCULATE(Count(Table[Start date]),filter('Table','Table'[Start date]<= _end && 'Table'[End Date]>=_St ))

    //use all('Date') if need in filter

     

     

     

    Other formula example

    new columns
    Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
    Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
    Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
    OR
    Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format

     

    measures
    This Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
    Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi amitchandak Thanks for your reply! It is not only based on today, but it could be any date within the current week (from Monday to Sunday) and the result should be filtered for the previous week, from Monday to Sunday.

      I tried your list of formulae but it's quite confusing and i couldn't get it to work... I don't understand your logic or approach to solving this problem.

      The Date[Year Week] is missing in my Calendar table, so i cannot use the following expression:

      Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format

      Wow... This is just a bunch of formulae dumped in here... I feel overwhelmed and really confused!!