Forum Discussion

Joshua_'s avatar
Joshua_
Helper II
4 years ago
Solved

Get days worked between two dates

Hi Guys,

I need to add a column that shows me the working day of the month, but starting and ending on a specific date,
for example started Jan 10 and ending Jan 25



I tried to create a column but the result was the total of the days.

NEW COLUMN =
var ini = MIN(CAMPANA[FEC_INICIO])
Var fin = MAX(CAMPANA[FEC_FIN])

return
SUMX(
FILTER(
CALENDARIO,
CALENDARIO[Date]>= ini && CALENDARIO[Date]<= fin
),
CALENDARIO[DIAS_HABILES]
)

I think the DATESMTD function is an option but I can't change the first day of the month.

thank a lot!

Need Help
  • Joshua_'s avatar
    Joshua_
    4 years ago

    HI parry2k ,

    I created a second table containing the dates of the new table and connected it with my calendar table, then I got the business days (related function) and with the following formula I can do it.

    CALCULATE(
    SUM(CALENDARIO_CAMPANA[DIAS_HABILES]),
    FILTER(
    CALENDARIO_CAMPANA,
    CALENDARIO_CAMPANA[Date]<=EARLIER(CALENDARIO_CAMPANA[Date]) && CALENDARIO_CAMPANA[CAMPANA] = EARLIER(CALENDARIO_CAMPANA[CAMPANA])
    )
    )



     

     

3 Replies

    • Joshua_'s avatar
      Joshua_
      Helper II
      4 years ago

      Hi parry2k ,

      It happens that I need to import a new table to the project and the dates of that data start and end on the 10th of every month, and I need to display the results based on the business days between those periods.

      • Joshua_'s avatar
        Joshua_
        Helper II
        4 years ago

        HI parry2k ,

        I created a second table containing the dates of the new table and connected it with my calendar table, then I got the business days (related function) and with the following formula I can do it.

        CALCULATE(
        SUM(CALENDARIO_CAMPANA[DIAS_HABILES]),
        FILTER(
        CALENDARIO_CAMPANA,
        CALENDARIO_CAMPANA[Date]<=EARLIER(CALENDARIO_CAMPANA[Date]) && CALENDARIO_CAMPANA[CAMPANA] = EARLIER(CALENDARIO_CAMPANA[CAMPANA])
        )
        )