topic Re: [WORKDAY] How to get the date moved forward [LT]column days in DAX Commands and Tips

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

ohnothimagain — Sun, 04 Aug 2024 00:38:49 GMT

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,

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

tharunkumarRTK — Sat, 03 Aug 2024 14:23:49 GMT

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

Please find the attached file

Need a Power BI Consultation? Hire me on Upwork

Connect on LinkedIn

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!

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

ohnothimagain — Sun, 04 Aug 2024 01:29:00 GMT

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.

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

tharunkumarRTK — Sun, 04 Aug 2024 04:05:07 GMT

ohnothimagain

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

Connect on LinkedIn

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!

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

ohnothimagain — Sun, 04 Aug 2024 06:01:12 GMT

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 )

anyway,Thank you for your help!