Forum Discussion

PowerZBI's avatar
PowerZBI
New Member
8 months ago
Solved

Calculated days

I have 2 dates and want to calculate working times between those and I want to calculate working days in a month using it. I have relationship between my date table and main table. Please guide me on the approach.

 

 

DAX
  • cengizhanarslan's avatar
    cengizhanarslan
    8 months ago

    Please try the formula below:

     

    Working Days in Month =
CALCULATE (
    COUNTROWS ( 'Date' ),
    WEEKDAY ( 'Date'[Date], 2 ) <= 5
)
  • AnkitKukreja's avatar
    AnkitKukreja
    8 months ago

    Hi! PowerZBI 


    You can use something like this, if I understood your problem. Check putting this in matrix visual

    Days Count =
    VAR WO_Start =
    CALCULATE(
    MIN('Table'[Date1]),
    ALL('Calendar')
    )

    VAR WO_End =
    CALCULATE(
    MAX('Labor Data'[Date2]),
    ALL('Calendar')
    )

    VAR StartDate = INT(WO_Start)
    VAR EndDate = INT(WO_End)

    VAR _days =
    COUNTROWS(
    FILTER(
    'Calendar',
    'Calendar'[CALENDAR_DATE] >= StartDate &&
    'Calendar'[CALENDAR_DATE] <= EndDate &&
    )
    )

    RETURN
    _days

     

     

     

     

     

     

     

4 Replies

  • cengizhanarslan's avatar
    cengizhanarslan
    Icon for Super User rankSuper User
    8 months ago

    Please try the formula below:

     

    Working Days in Month =
CALCULATE (
    COUNTROWS ( 'Date' ),
    WEEKDAY ( 'Date'[Date], 2 ) <= 5
)
  • FBergamaschi's avatar
    FBergamaschi
    Icon for Super User rankSuper User
    8 months ago

    Hi PowerZBI 

    what do you mean

     

    want to calculate working times ?

     

    Did you mean working days?

     

    Furthermore, how do you define a working day? Whatever day is not Saturday or Sunday? Do you need to consider holydays / closing days also ?

     

    If this helped, please consider giving kudos and mark as a solution

    @me in replies or I'll lose your thread

    Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

    Consider voting this Power BI idea

    Francesco Bergamaschi

    MBA, M.Eng, M.Econ, Professor of BI

  • AnkitKukreja's avatar
    AnkitKukreja
    Icon for Super User rankSuper User
    8 months ago

    Hi! PowerZBI 


    You can use something like this, if I understood your problem. Check putting this in matrix visual

    Days Count =
    VAR WO_Start =
    CALCULATE(
    MIN('Table'[Date1]),
    ALL('Calendar')
    )

    VAR WO_End =
    CALCULATE(
    MAX('Labor Data'[Date2]),
    ALL('Calendar')
    )

    VAR StartDate = INT(WO_Start)
    VAR EndDate = INT(WO_End)

    VAR _days =
    COUNTROWS(
    FILTER(
    'Calendar',
    'Calendar'[CALENDAR_DATE] >= StartDate &&
    'Calendar'[CALENDAR_DATE] <= EndDate &&
    )
    )

    RETURN
    _days