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

## [WORKDAY] How to get the date moved forward [LT]column days

Hello everyone,
now I'm struggling with a problem related WORKDAY...
I want to get the date that move [M_date] forward  [LT] days on workdays.
→it's equal to Excel function WORKDAY([M_date,-[LT],[Holi]) but I couldn't figure out....(I'm doing it on PowerPivot)

e.g. (Left below; fact table,a row colored yellow  Right; calender table)
M_date is 19/8/2024,LT is 2days,the date wanted is 8/8/2024.

How can I make a formula for calculated column (and measure,if possible)?

Thank you for your continuing support and kindness!
Best Regards,

1 ACCEPTED SOLUTION
Super User

Though I wont recommend calculated columns, here is forumula:

var Holiday=filter('Calendar', 'Calendar' [Date]<current _date&&'Calendar'[1* E]='*')

var LT = TEST_'(PN] var F_Date=topn(LT,Holiday, 'Calendar'[Date], DESC)

var Result=minx(F_Date, 'Calendar [Date])

return Result

topn is used on a calendar dimension table, so I dont it would cause any performance issues

Need a Power BI Consultation? Hire me on Upwork

 Did I answer your question? Mark my post as a solution! If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!

4 REPLIES 4
Super User

@ohnothimagain
There are multiple ways to acheive this, one option is to use NETWORKDAYS function in DAX.
NETWORKDAYS – DAX Guide

I tried a different approach and did not use networkdays()

Need a Power BI Consultation? Hire me on Upwork

 Did I answer your question? Mark my post as a solution! If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!

Helper I

Thank you so much! it worked,I have no idea for using TOPN.
and I'm afraid to say that I ask you an additional request,how do you write a formula
for calculated columns?

I tried following formula and it seems to be successed.but I'm concerning its processing cost.
*sorry for incovenience for displaying in my native language.

Super User

Though I wont recommend calculated columns, here is forumula:

var Holiday=filter('Calendar', 'Calendar' [Date]<current _date&&'Calendar'[1* E]='*')

var LT = TEST_'(PN] var F_Date=topn(LT,Holiday, 'Calendar'[Date], DESC)

var Result=minx(F_Date, 'Calendar [Date])

return Result

topn is used on a calendar dimension table, so I dont it would cause any performance issues

Need a Power BI Consultation? Hire me on Upwork

 Did I answer your question? Mark my post as a solution! If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!

Helper I

Ah,I've misunderstood about row context...Did it!
I need a numeric table lke this,so I considered it's necessary to make a relationship.
(if there's another way,I’d greatly appreciate it )

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.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors