Forum Discussion

Trebor84's avatar
Trebor84
Helper 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 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"
Need Help

2 Replies

  • jbwtp's avatar
    jbwtp
    Memorable 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

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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