Forum Discussion

game1's avatar
game1
Helper III
2 years ago

Filter date for the last 7days

Hello!
I would like to create a new column to filter the TABLE[Date] which is in YYYY-MM-DD HH:mm:ss format, so that it just returns me the list of the last 7 days when I choose the box last7days,  I would have only the dates of the last 7 days and for the list of the last 30 days,  I would have only the dates of the last 30 days.

So, I want 2 box: 

last7days

last30days


THANKS.

Show and Tell
Tutorial Requests

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    2 years ago

    game1 I would add an offset column to your data which is the number of days from today. So, for example, start with today being 0 and it increments up from there. Then you could build a simple measure that filters for <=7 and <=30. Can be done in Power Query or DAX. Here is the DAX version:

    Sequential - Microsoft Fabric Community

    • game1's avatar
      game1
      Helper III
      2 years ago

      I use datesDiff =
      SWITCH(
      TRUE(),
      Table[Date] >= TODAY() - 7 && Table[Date] < TODAY(), "7 last days",
      Table[Date] >= TODAY() - 30 && Table[Date] < TODAY(), "30 last days",
      TRUE(), "more than 30 days"
      )

      but, it is only give the good range for the first one (7 last days). For the other, the range are not good. Why? How correct that?

  • game1's avatar
    game1
    Helper III
    2 years ago

    Not functionnig. I try this:

    datesDiff = if(datediff(Table[Date], Today(), DAY) <=7, "7 last days", "more than 7 days") but I dotn have the good range. For example, from today, it's supposed to return me the lsit of date beetween 2023-12-07 to 2023-12-13. Where is the problem? Do I have other option? Thanks!