Display of WeekNumber with the Day name and Date

[Anonymous]

Hi,

        I have a requirement where i need to show the weekly progress of data. I have used Weeknum function to pull the week number from the available date column.

But client wants to see the axis display as Monday and the date for that particular week. I tried using FORMAT but it pulled day and date for all values and its affecting the display. 

Eg, Instead of weeknumber in axis i need it be displayed as "Mon 12" instead of this weeknumber 14

Code i used to generate the weeknumber

Week_Number = WEEKNUM(Orders[Order Date],2)

Kindly help to get the weeknumber to be displayed in the format i want.

I am not able to attach the sample PBIX file but hope this info is enough to get the solution.

[Jihwan_Kim]

Hi, @Anonymous 

I am not quite sure whether I understood your question correctly.

Please check the link down below, and look for the customs column in the table whether that is what you are looking for.

I could not create this by using DAX, so I created this column in Power Query Editor.

If you check inside the power query editor in my sample pbis file, it is not difficult to follow to create this.

I hope I understood correctly, otherwise please let me know.

Thank you.

https://www.dropbox.com/s/iun7b2r9m1tdbq4/monday%20with%20datenumber%20only.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: https://www.linkedin.com/in/jihwankim1975/

[Anonymous]

Hi Kim,

            The solution worked exactly as i wanted. But there is an issue. I am not able to replicate the Filldown option at the last as the data contains many date points for the same date. Ultimately i need a feature where the values are filled based on the week rather than Fill down as that creates some issue.

Kindly suggest the best possible way to replace the values complely instead of using Fill Down.

Thanks in Advance!

[v-easonf-msft · Accepted solution]

Hi， @Anonymous 

(Eg., Current week is 14 - It should be represented as "Monday 12" instead of 14)

If I understand this sentence correctly,it is suggested to create a calculated column .

Please try  formula as below to format your date column:

Result =
VAR weekday =
    WEEKDAY ( 'Calendar'[Date], 2 )
VAR firstvalueofweek = 'Calendar'[Date] - weekday + 1
RETURN
    "Mon" & " "
        & FORMAT ( firstvalueofweek, "DD" )

Result will show as below:

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Jihwan_Kim]

Hi, @Anonymous 

Thank you for your feedback.

I am not quite sure if I understood your question correctly, but if you have the issue like, "the data contains many date points for the same date", it means that this is not the DIM calendar table. Am I correct?

I do not know your actual model and the case well, but I think it is better to create your desired column in a DIM calendar table.

[amitchandak]

@Anonymous ,

Try a column like

Date format = format([Date], "DDD DD")

or

Date format = format([Date], "DDD") &" " &Date format = format([Date], "DD ") 

[Anonymous]

Hi,

      Thanks for the response. I have used the same format but the problem is i need to get the particluar weeks day as Monday and the date for it.

Eg., Current week is 14 - It should be represented as "Monday 12" instead of 14. 

Normal format just gives out all the available dates in the rqd format.