cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
paulfink
Post Patron
Post Patron

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 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

 

image.png

 

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:

 

image.png

 

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

 

Here is my desired output:

image.png

 

 

 

 

 

 

 

 

 

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

1 ACCEPTED SOLUTION
Eyelyn9
Community Support
Community Support

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 )

10.27.6.2.PNG

My final visualization looks like this:

 

 10.27.6.1.PNG

 

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

View solution in original post

3 REPLIES 3
Eyelyn9
Community Support
Community Support

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 )

10.27.6.2.PNG

My final visualization looks like this:

 

 10.27.6.1.PNG

 

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
Super User
Super User

@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.



Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Learn Power BI P&L Statement || Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s ||
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!!! How to get Quick Help !!
Resources | Master Power BI !! Expertise Power BI !! Power BI For Tableau User !!

@amitchandak i have updated my post

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.