Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
v-eqin-msft
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
v-eqin-msft
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.

@amitchandak i have updated my post

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors