How would I write a DAX Yes/No column to indicate if the row is within the last week or last 6 weeks

[Shawry]

Hi,

I need to a display a few graphs where some need to display last week's data, and some that need to display the last 6 weeks worth of data. 

I'm thinking I need a boolean column to indicate whether the row is within the last week, or if it's within the last 6 weeks. 

Is this the best approach? And if so, how would I go about implementing this? Thanks!

[FreemanZ]

hi @Shawry 

yes, good practice. try like:

Column =

SWITCH(

    TRUE(),

    [Date]>=TODAY()-42&&[Date]<TODAY()-7,

   "Last 1-6 Weeks",

   [Date]>=TODAY()-7&&[Date]<TODAY(),

   "Last Week", " "

)

[Shawry]

Amazing, thank you! Follow-up question, is it difficult to get it to work off a calendar week i.e. Monday - Sunday? If it is, this solution will probably work for me 🙂

[FreemanZ]

@Shawry 

could you elaborate what does this mean: "get it to work off a calendar week i.e. Monday - Sunday"?

[ahmadibrahimbus]

Dear @Shawry ,

here my suggestion to solve your request.

create a table using the below M language in query editor:

let
TodaysDate = Date.From(DateTimeZone.FixedUtcNow()),
Ranges = {
{"Last Week Date", Date.AddDays(TodaysDate,-7), TodaysDate, 1},
{"Last 6 Weeks Dates",Date.AddWeeks(TodaysDate,-6), TodaysDate, 2}
},
fxCreatePeriodTabe = ( PeriodName as text, StartDate as date, EndDate as date, SortOrder as number ) as table =>
let
DayCount = Duration.Days(EndDate-StartDate)+1,
DateList = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
AddPeriodName = List.Transform(DateList, each {PeriodName,_,SortOrder}),
CreateTable = #table( type table[Period=text, Date=date, Sort=number], AddPeriodName)
in
CreateTable,

GetTables = List.Transform(Ranges, each fxCreatePeriodTabe(_{0}, _{1}, _{2}, _{3})),
Output = Table.Combine(GetTables),
#"Filtered Rows" = Table.SelectRows(Output, each ([Period] = "Last 6 Weeks Dates"))
in
#"Filtered Rows"

linke calender table to this table 1 to many. and enable both side so you will be able to filter the calender table using period column in the new created table.

hope this helps.