Weekly Target

[paulfink]

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 for the whole week but this is not effective as days in other months are getting caught in this.

Weekly Date = 'Closed Calendar Table'[Date] - WEEKDAY('Closed Calendar Table'[Date], 2) +1

I need to change this or make a new formula that keeps months seperate and not overlap in previous months weekly date

As you can see, the August date is being pulled over the September dates. I need these spready so that, e.g 31st August 2020 is its own date and 1st September 2020 is pulled down for the rest of the week.

I am using this for targets. This is my Shared Axix on my Clustered Column Chart:

So in the middle, 31 August 2020 will be its own date then have 01 September 202 next. 

Here is my desired output:

As you can see for September, the Weekly goes up to the end of the Month then continues for October.

[v-eqin-msft · Accepted solution]

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

[amitchandak]

@paulfink , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.

[paulfink]

@amitchandak i have updated my post