Calculated Column Time Intelligence

[Anonymous]

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

[Anonymous · Accepted solution]

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.