Forum Discussion

paulfink's avatar
paulfink
Post Patron
5 years ago
Solved

Weekly Target

Hi guys,   need some help altering my reports format.   It is currently set as Weekly but the weekly target formula needs changing.   Currently it is taking the first date and spreading it over...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi paulfink ,

    I suggest operating in Power Query:

    Add Day and DateDay column --> Add a conditional column(when the Day=monday or DateDay=1 return the current date) -->Use Fill down .

     

    The full formula in Advanced Editor is as follows:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrLQN7DUNzIwMlDSUQpOLdAFMmJ1gOKW2MWNDbCLGxjqGxrAxP2TS+DiRjjEjXGIm+AQN8UhboZD3ByHuAUOcUvs4kBB7OLY/BsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Period = _t]),
    #"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"Date", type date}}, "en-GB"),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type with Locale", "Index", 1, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each Date.DayOfWeekName([Date])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "DateDay", each Date.Day([Date])),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom1",{{"Custom", "Day"}}),
    #"Added Conditional Column" = Table.AddColumn(#"Renamed Columns", "Custom", each if [Day] = "Monday" then [Date] else if [DateDay] = 1 then [Date] else null),
    #"Filled Down" = Table.FillDown(#"Added Conditional Column",{"Custom"})
in
    #"Filled Down"

    Then add a rank column for conditional formatting:

    Column =
RANKX ( 'Table', [Custom],, ASC, DENSE )

    My final visualization looks like this:

     

     

     

    Here is my pbix file.

     

    Did I answer your question ? Please mark my reply as solution. Thank you very much.
    If not, please upload some insensitive data samples and expected output.

     

    Best Regards,
    Eyelyn Qin