Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Probably has been discussed, but looking for some assistance as i have not found anything online. Is there a way to add a custom column to a specific date (ex. 9/1/2021) and it returns how many "Mondays" are in that month?
Solved! Go to Solution.
Here is a DAX column expression that shows one way to do it. Replace T1 with your actual table name. Note that you should make your column type Date instead of type DateTime.
Mondays =
VAR thisdate = T1[Week]
VAR thisEOM =
EOMONTH ( thisdate, 0 )
VAR thisCalendar =
CALENDAR ( thisdate, thisEOM )
VAR result =
COUNTROWS ( FILTER ( thisCalendar, WEEKDAY ( [Date] ) = 2 ) )
RETURN
result
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@Jakinta pretty slick!
Here is a DAX column expression that shows one way to do it. Replace T1 with your actual table name. Note that you should make your column type Date instead of type DateTime.
Mondays =
VAR thisdate = T1[Week]
VAR thisEOM =
EOMONTH ( thisdate, 0 )
VAR thisCalendar =
CALENDAR ( thisdate, thisEOM )
VAR result =
COUNTROWS ( FILTER ( thisCalendar, WEEKDAY ( [Date] ) = 2 ) )
RETURN
result
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
You can add this step to ypur query.
I have used Date.StartOfMonth as caution in case the date in Week column is not 1st day of month.
= Table.AddColumn( PriorStepName , "MondaysCount", each let s=Number.From(Date.StartOfMonth(Date.From([Week]))), e=Number.From(Date.EndOfMonth(Date.From([Week]))), Dates=List.Transform({s..e}, Date.From) in List.Count( List.Select( Dates, each Date.DayOfWeek(_)=1 ) ) )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.