cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## 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!

1 ACCEPTED SOLUTION
Helper I

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 3
Super User

@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.

Helper I

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.

Helper I

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.