Date.DayOfWeek with Date.IsInPreviousNDays

[Trebor84]

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 Sunday then don't show Sunday, Saturday or Friday.

Also on any given day dont show that days data.  Can anyone assist with adapting the below to be able to achieve this please?

if Date.DayOfWeek(Date.From(DateTime.LocalNow()),Day.Sunday) = 0 then Date.IsInPreviousNDays([date],2) else "FALSE"

[v-cgao-msft]

Hi @Trebor84 ,

For a visual with a date axis, I would consider adding a filter to the visual.

Measure = 
VAR _today = TODAY()
VAR _weekday = WEEKDAY(_today,2)
VAR _filter =
SWITCH(
    TRUE(),
    NOT _weekday IN {6,7} && SELECTEDVALUE('Table'[Date]) < _today, 1,
    _weekday = 6 && SELECTEDVALUE('Table'[Date]) < _today-1, 1,
    _weekday = 7 && SELECTEDVALUE('Table'[Date]) < _today-2, 1
)
RETURN
_filter

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[jbwtp]

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