Calculated Column Time Intelligence

Anonymous — Fri, 07 Jul 2023 15:37:15 GMT

Good afternoon,
would like to return a target column that shows the Month which has higher working weeks in the selected week

For example target return shall be March for week 15 as of having 5 to 0 in comparing (march - april).

Thanks

W_Days IN week = var x = CALCULATE(count('Calendar'[Month]), ALLEXCEPT('Calendar','Calendar'[Weeknum],'Calendar'[Month]),WEEKDAY('Calendar'[Date],2)<>7 && WEEKDAY('Calendar'[Date],2)<>6) var y = SWITCH(TRUE(),isblank(x)=TRUE(),0,x) return y

Re: Calculated Column Time Intelligence

Anonymous — Mon, 10 Jul 2023 03:06:25 GMT

Hi Anonymous

Please have a try.

Working Weeks = VAR CurrentWeek = [Week] VAR CurrentMonth = MONTH ( [Date] ) VAR NextMonth = MONTH ( DATE ( YEAR ( [Date] ), MONTH ( [Date] ) + 1, 1 ) ) VAR DaysInCurrentMonth = DAY ( EOMONTH ( [Date], 0 ) ) VAR DaysInNextMonth = DAY ( EOMONTH ( [Date], 1 ) ) VAR CurrentWeekDays = SWITCH ( CurrentWeek, 1, 7, 2, 6, 3, 5, 4, 4, 5, 3, 6, 2, 7, 1 ) VAR NextMonthDays = 7 - CurrentWeekDays VAR CurrentMonthWorkingDays = DaysInCurrentMonth - CurrentWeekDays VAR NextMonthWorkingDays = DaysInNextMonth - NextMonthDays VAR WorkingWeeks = IF ( CurrentMonthWorkingDays >= 5, 5, CurrentMonthWorkingDays ) / 5 + IF ( NextMonthWorkingDays >= 5, 5, NextMonthWorkingDays ) / 5 RETURN WorkingWeeks

Then create another column.

Target Month = VAR CurrentMonth = MONTH ( [Date] ) VAR NextMonth = MONTH ( DATE ( YEAR ( [Date] ), MONTH ( [Date] ) + 1, 1 ) ) VAR CurrentMonthWorkingWeeks = [Working Weeks] VAR NextMonthWorkingWeeks = CALCULATE ( SUM ( [Working Weeks] ), DATESBETWEEN ( 'Table'[Date], DATE ( YEAR ( 'Table'[Date] ), NextMonth, 1 ), DATE ( YEAR ( 'Table'[Date] ), NextMonth, 7 ) ) ) RETURN IF ( CurrentMonthWorkingWeeks >= NextMonthWorkingWeeks, CurrentMonth, NextMonth )

Add the "Target Month" calculated column to your visualizations to see the month with the highest number of working weeks for each week.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

topic Calculated Column Time Intelligence in DAX Commands and Tips

Calculated Column Time Intelligence

Re: Calculated Column Time Intelligence