cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MR
Regular Visitor

Display Data by Week day - ie Mon, Tue etc

Hi 

 

I need to sum multple event hours which happen on a given day of the week during a set time period.

For instance, I want to see the sum of events hours over a month in columns Monday, Tuesday, through to Sunday. This is to see the supply or demand on a given day of the week.

 

How can I sum a these hours by week days?

 

Any ideas?

Matt

1 ACCEPTED SOLUTION
Phil_Seamark
Microsoft
Microsoft

Just add a column to your data table, or to your Date table along these lines

 

Weekday = FORMAT(Dates[Date],"DDDD")

And then just drag that column to the axis of your visual.


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

7 REPLIES 7
Anonymous
Not applicable

Doesn't seem to work for DAX.  "Function 'FORMAT' is not allowed as part of calculated DAX expressions on DirectQuery models."


 

Expression.Error: The name 'FORMAT' wasn't recognized. Make sure it's spelled correctly.

Phil_Seamark
Microsoft
Microsoft

Just add a column to your data table, or to your Date table along these lines

 

Weekday = FORMAT(Dates[Date],"DDDD")

And then just drag that column to the axis of your visual.


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

this does not work,  I copied and pasted get too many arguments

Or in Power Query:

 

#"Day of Week" = Table.AddColumn(#"__prior command name__", "__desired new column name__", each Date.DayOfWeekName([__column referencing__], "en-US"), type text)
in
#"Day of Week"

Anonymous
Not applicable

This also works as part of the addcolumn query when building the Date Table.

For Example: 
Date =
ADDCOLUMNS (
CALENDAR (DATE(2000,1,1), DATE(2025,12,31)),
"Weekday", FORMAT([Date], "DDDD")
)

Excellent, thanks Phil

M

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors