Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I'm trying to get a calculated column that returns a true or false if a certain date falls within 15 working days.
I have 2 tables, Table 1 and a date table.
Table 1's calculated column =
Measure 1 =
IF (
DATEVALUE (
'Table 1'[PO Date] ) > ( TODAY () - 15 ),
TRUE (),
FALSE())
^ this only looks at the last 15 days but I need working days.
My date table is linked and and contains a list of all working/ non working days.
So I need help amending the code, so it returns a True if it's over 15 working days or False if it's within 15 working days.
Solved! Go to Solution.
@Anonymous ,
It seems only weekdays, i.e. Mon-Fri, are defined as working days in your model, without taking into consideration any holidays. If so, you even don't bother to use a calendar table.
=
TODAY () - 21 <= [PO Date] && [PO DATE] < TODAY ()
&& WEEKDAY ( [PO DATE], 2 ) < 6
//whatever day is today, previous 21 days surely contain exact 15 working days
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
@Anonymous ,
It seems only weekdays, i.e. Mon-Fri, are defined as working days in your model, without taking into consideration any holidays. If so, you even don't bother to use a calendar table.
=
TODAY () - 21 <= [PO Date] && [PO DATE] < TODAY ()
&& WEEKDAY ( [PO DATE], 2 ) < 6
//whatever day is today, previous 21 days surely contain exact 15 working days
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
@Anonymous , Have these column in your date table
Work Day = if(WEEKDAY([Date],2)>=6,0,1)
Work Date = if(WEEKDAY([Date],2)>=6,BLANK(),[Date])
Work Date Cont = if([Work Day]=0,maxx(FILTER('Date',[Date]<EARLIER([Date]) && [Work Day]<> EARLIER([Work Day]) ),[Date]),[Date])
Work Date cont Rank = RANKX(ALL('Date'),[Work Date Cont],,ASC,Dense)
This Day = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Work Date cont Rank]=max('Date'[Work Date cont Rank])))
Last work day = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Work Date cont Rank]=max('Date'[Work Date cont Rank])-1))
Last 15 work day = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Work Date cont Rank]>=max('Date'[Work Date cont Rank])-15))
refer
Traveling Across Workdays - What is next/previous Working day
https://community.powerbi.com/t5/Community-Blog/Travelling-Across-Workdays-Decoding-Date-and-Calendar-4-5-Power/ba-p/1187766
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.
Hi @amitchandak ,
I can't seem to get this to work for me. I've created a test document for you so you can see what I'm working with.
https://1drv.ms/u/s!AnIEh6WhI4Jogv5SHwsMx0mFuwM7iQ?e=0ZWXNg
Essentially, I just need a way to calculate the date of "PO Date" - "15 Working days"
So for example
PO DATE PO - 15 working days
27/11/2020 06/11/2020
Thanks in Advance.
Karlos. .
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
24 | |
20 | |
15 | |
12 |
User | Count |
---|---|
55 | |
48 | |
43 | |
19 | |
18 |