Calculated Column on Analysis Services with Tabular Editor

[Anonymous]

Hello everyone and thanks in advance for anyone who can help me.
The goal of this topic (for more details I have attached the example file), is to create a calculated column (WD Position Ranking) that allows you to assign the corresponding value to each working day (e.g 1,2,3,4 ecc...) and giving value 0 to the days of the week as weekends and holidays. Also, for each new month the counter will have to reset and start at 0 again.

The column that identifies me if the day is a worker day is "IsWorkerDay" while the column that identifies me the month change is "Month_Name".

Thank you so much again

[Anonymous · Accepted solution]

Hello @Fabio_RM86

I build two tables to achieve your goal.

Date table:

Date = 
ADDCOLUMNS (
    CALENDAR ( DATE ( 2020, 01, 01 ), DATE ( 2020, 12, 31 ) ),
    "Year", YEAR ( [Date] ),
    "Month", MONTH ( [Date] ),
    "Day", DAY ( [Date] ),
    "Quarter", QUARTER ( [Date] ),
    "Week", WEEKNUM ( [Date], 1 ),
    "DayName", FORMAT ( [Date], "DDDD" ),
    "MonthName", FORMAT ( [Date], "MMMM" )
)

DayofYear = RANKX('Date','Date'[Date],,ASC)

Holiday table:

Two related tables per date column and vacation column.

Next, add an IsworkingDay column to DateTable.

IsWorkingday = IF(RELATED(Holiday[HolidayName]) = BLANK(),IF(OR('Date'[DayName]="Saturday",'Date'[DayName]="Sunday"),0,1), 0)

Finally build WD_Position_Ranking

WD_Position_Ranking = 
IF (
    'Date'[IsWorkingday] = 0,
    0,
    RANKX (
        FILTER (
            'Date',
            'Date'[Year] = EARLIER ( 'Date'[Year] )
                && 'Date'[Month] = EARLIER ( 'Date'[Month] )
                && 'Date'[IsWorkingday] <> 0
        ),
        'Date'[Date],
        ,
        ASC
    )
)

The result is as follows.

If you have other holidays, you can add it to the holiday table and get the new result.

You can download the pbix file from this link: Calculated column in Analysis Services with Tabular Editor

Best regards

Rico Zhou

If this post helps,then consider Accepting it as the solution to help other members find it faster.

[Anonymous · Accepted solution]

Hello @Fabio_RM86

Could you tell me if your problem has been solved? If so, here's the solution. More people will benefit from it. Or you are still confused about it, please provide more details about your table and your problem or share me with your pbix file from your Onedrive for Business.

Best regards

Rico Zhou

[amitchandak]

@Anonymous , Assume you have work Day

Workday = WEEKDAY([Date],2)

Month Work Day
month Day = sumx(filter(date, [Date] <earlier([Date])),[Workday])

[Anonymous]

Hi Amitchandak and thanks for your support but the dax code don't work well.

I try to implement your code but as you seen on screenshot the column doesn't return the correct value.

I'd like to have the progressive counter and when change it the month the counter start to 0 and recalculate again same logic.

[Anonymous · Accepted solution]

Hello @Fabio_RM86

I build two tables to achieve your goal.

Date table:

Date = 
ADDCOLUMNS (
    CALENDAR ( DATE ( 2020, 01, 01 ), DATE ( 2020, 12, 31 ) ),
    "Year", YEAR ( [Date] ),
    "Month", MONTH ( [Date] ),
    "Day", DAY ( [Date] ),
    "Quarter", QUARTER ( [Date] ),
    "Week", WEEKNUM ( [Date], 1 ),
    "DayName", FORMAT ( [Date], "DDDD" ),
    "MonthName", FORMAT ( [Date], "MMMM" )
)

DayofYear = RANKX('Date','Date'[Date],,ASC)

Holiday table:

Two related tables per date column and vacation column.

Next, add an IsworkingDay column to DateTable.

IsWorkingday = IF(RELATED(Holiday[HolidayName]) = BLANK(),IF(OR('Date'[DayName]="Saturday",'Date'[DayName]="Sunday"),0,1), 0)

Finally build WD_Position_Ranking

WD_Position_Ranking = 
IF (
    'Date'[IsWorkingday] = 0,
    0,
    RANKX (
        FILTER (
            'Date',
            'Date'[Year] = EARLIER ( 'Date'[Year] )
                && 'Date'[Month] = EARLIER ( 'Date'[Month] )
                && 'Date'[IsWorkingday] <> 0
        ),
        'Date'[Date],
        ,
        ASC
    )
)

The result is as follows.

If you have other holidays, you can add it to the holiday table and get the new result.

You can download the pbix file from this link: Calculated column in Analysis Services with Tabular Editor

Best regards

Rico Zhou

If this post helps,then consider Accepting it as the solution to help other members find it faster.

[Anonymous]

Thank a lots,

The solutions is a great 😉

Have a nice day

Fabio