cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
anwilkins
Helper I
Helper I

Month & Week Number in a Tabular Grid

I was asked to re-create in Power BI a tabular report where the dates have been manually manipulated. The data is derived from a SharePoint list with limited fields. The list contains a month and week of projected and actual billed hours. I have built a custom calendar and linked it to Create Date however, the report is not driven by that date field so it doesn't help and my only real issue is in getting the  "for Month" to sort in my visual. 

Here is the original report:

anwilkins_1-1629989730815.png

My version in Power BI

anwilkins_0-1629989529938.png

Here is my calendar

Calendar =
var _calendar = CALENDAR("07/01/2021", "12/31/2023")
return
ADDCOLUMNS(_calendar,
"Year", Year([Date]),
"MonthNo", MONTH([Date]),
"Month", FORMAT([Date], "mmmm"),
"Day", DAY([Date]),
"Day No", WEEKDAY([Date],2),
"DOW", FORMAT([Date], "DDD"),
"Quarter", "QTR" & FORMAT([Date], "Q"))
 
Any idea on how to get the month to sort or to add in the dates of the weeks would be super helpful!

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@anwilkins ,

You need to create a sort column and mark that as sort column

 

Month Year = FORMAT([Date],"mmm")
Month Year sort = FORMAT([Date],"mm")

 

Mark Month Year sort as sort column of Month Year 

 

Sort Month Year.png

 

How to Create Sort Column and Solve Related Errors:
https://www.youtube.com/watch?v=KK1zu4MBb-c

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

View solution in original post

2 REPLIES 2
anwilkins
Helper I
Helper I

Thanks for the idea but my calendar was hosing things up so I took it out of the mix, used the Create date from SharePoint and then created sort on that date. As always...I appreciate this community!!!

amitchandak
Super User
Super User

@anwilkins ,

You need to create a sort column and mark that as sort column

 

Month Year = FORMAT([Date],"mmm")
Month Year sort = FORMAT([Date],"mm")

 

Mark Month Year sort as sort column of Month Year 

 

Sort Month Year.png

 

How to Create Sort Column and Solve Related Errors:
https://www.youtube.com/watch?v=KK1zu4MBb-c

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.