Different days usinf custom column

[Banan]

Hi,

I'm using DirectQuery and i want to calculate the start date - end date without the weekend (Friday and Saturday) through a custom column. Can you help me?

[Anonymous]

Hi @Banan 

If you want to add the custom column in power query in direct query mode,  the custom column cannot be applied, becuse in direct mode, the steps in power query have limitations,you can refer to the following link.

https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-directquery-about#data-transformatio...

The direct mode have limitations in transform data and data modeling, you can refer to the following link.

https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-directquery-about#modeling-limitatio...

The best way is to change the direct mode to import mode, then you can use the custom column, you can refer to the following sample.(the connect mode is import)

Sample data 

Add a custom column

List.Sum(List.Transform(List.Dates([dt1],Duration.Days([dt2]-[dt1])+1,#duration(1,0,0,0)),each if Date.DayOfWeek(_,Day.Sunday)<5 then 1 else 0))

Output

If you cannot change the mode to import mode, please consider to create a measure, you can refer to the following measure.

Measure =
VAR a =
    ADDCOLUMNS (
        CALENDAR ( MIN ( SQLTest[dt1] ), MAX ( SQLTest[dt2] ) ),
        "Weekday", WEEKDAY ( [Date], 2)
    )
RETURN
    COUNTROWS ( FILTER ( a, [Weekday] <> 5 && [Weekday] <> 6 ) )

Output

Best Regards!

Yolo Zhu

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

[Banan]

There is no error in the code, but when I create a chart and use the new measure, this message appears " MdxScript(Model) (8,8) calculation error in measure "table name[Measure]: the start date in calender function can not be later than end date". 

Note: The weekend in our country is Friday and Saturday

[Banan]

There is no error in the code, but when I create a chart and use the new measure, this message appears " MdxScript(Model) (8,8) calculation error in measure "table name[Measure]: the start date in calender function can not be later than end date". 

Note: The weekend in our country is Friday and Saturday

[Anonymous · Accepted solution]

Hi @Banan 

When you use the calendar() funciton, then first date you need to input the start date, then second date you need to input the end date, the end date need to be greater than the start date, for the error message you offered , you didn't put the start date in the first date. You can refer to the following link.

CALENDAR function (DAX) - DAX | Microsoft Learn

Best Regards!

Yolo Zhu

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