Solved: Get days worked between two dates

Joshua_ · ‎01-13-2022

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!

Joshua_ · ‎01-13-2022

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])

)

View solution in original post

parry2k · ‎01-13-2022

@Joshua_ all month start from 10 and ends at 25th? what's the logic?

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Joshua_ · ‎01-13-2022

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_ · ‎01-13-2022

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])

)