Forum Discussion

Trebor84's avatar
Trebor84
Icon for Helper II rankHelper II
3 years ago

Date.DayOfWeek with Date.IsInPreviousNDays

Hi, I'm trying to find a way to avoid my Power Bi report showing data over the weekend when the report refreshes.   If today is Saturday then don't show Saturdays data or Fridays. If today is Sun...
Need Help
jbwtp's avatar
jbwtp
Icon for Memorable Member rankMemorable Member
3 years ago

Hi Trebor84,

 

I think you may be after something like this:

Table.AddColumn(#"Expanded Expand", "Check", each Date.DayOfWeek([today_is]) > 4 and Date.AddDays([today_is], -3 + 6 - Date.DayOfWeek([today_is])) < [date] )

This is how it works:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc65DQAgDATBXhwj4TN/LRb9t0HKhpNNpqm6ang0uyUtoAZ1aEATWtCGDiQnuRE74kdf6D4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"date", type date}}),
    DoW = Table.AddColumn(#"Changed Type", "DoW", each Date.DayOfWeekName([date]), type text),
    
    today_is = Table.TransformColumnTypes(Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtI3MNQ3MjAyVorViVYyRuGZoPBMUXhmKDxzFJ6FPpADZMYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable date) meta [Serialized.Text = true]) in type table [today_is = _t]),{{"today_is", type date}}),
    #"Added Custom" = Table.AddColumn(today_is, "Expand", each DoW),
    #"Expanded Expand" = Table.ExpandTableColumn(#"Added Custom", "Expand", {"date", "DoW"}, {"date", "DoW"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Expand", "Check", each Date.DayOfWeek([today_is]) > 4 and Date.AddDays([today_is], -3 + 6 - Date.DayOfWeek([today_is])) < [date] )
in
    #"Added Custom1"

 

Cheers,
John