Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Calculated Column Time Intelligence

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
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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.

     

     

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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.