I can't get the result as 21.5

[koray]

Hello,

I have a table in my Excel file that shows the total working days of the month based on the date.

I don't see the total number of working days as 21.5 in my report. That puts it at 22. How do I fix this?

Thank you.

[v-alq-msft]

Hi, @koray 

I wonder what the measure is like. Based on your description, I created data to reproduce your scenario.

Table:

Calendar(a calculated table):

Calendar = CALENDARAUTO()

There is no relationship between two tables.

You may create two measures as below. They are both set as decimal number.

Working Days = 
var _date = SELECTEDVALUE('Table'[Date])
var _year = YEAR(_date)
var _month = MONTH(_date)
var _result = 
CALCULATE(
    DISTINCTCOUNT('Calendar'[Date]),
    FILTER(
        ALL('Calendar'),
        YEAR('Calendar'[Date])=_year&&
        MONTH('Calendar'[Date])=_month&&
        NOT(WEEKDAY('Calendar'[Date]) in {1,7})&&
        'Calendar'[Date]<_date
    )
)
return
IF(
    ISBLANK(_result),
    0,
    _result
)

Remain Working Days = 
var _date = SELECTEDVALUE('Table'[Date])
var _year = YEAR(_date)
var _month = MONTH(_date)
var _result = 
CALCULATE(
    DISTINCTCOUNT('Calendar'[Date]),
    FILTER(
        ALL('Calendar'),
        YEAR('Calendar'[Date])=_year&&
        MONTH('Calendar'[Date])=_month&&
        NOT(WEEKDAY('Calendar'[Date]) in {1,7})
    )
)
return
_result-[Working Days]+0.5

Result:

Best Regards

Allan

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

[jptak]

What is the data type set to for that column in Power BI? Looks like it may be set to Whole Number.

[koray]

Hello,

I tried many option, but 😞

[jptak]

What is the data type in Power Query?

[koray]

What should it be? I sent it in the first picture.

[az38]

@koray  hi

check:

a) Column tools ribbon for [Kalan Gun] column

b) Date type for  [Kalan Gun] in Power Query. It should be Decimal number