Skip to main content
cancel
Showing results for 
Search instead 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

Reply
KW123
Helper V
Helper V

Filtering out for Holidays & Descending order

Hi! I have a two part question. 

I have this calulated column:

FD = SUMX (
FILTER (
'Dates',
[Date] <= EARLIER ( 'Dates'[Date] )
&& WEEKDAY ( EARLIER ( 'Dates'[Date] ), 2 ) <> 7
&& MONTH ( [Date] ) = MONTH ( EARLIER ( 'Dates'[Date] ) )
&& YEAR ( [Date] ) = YEAR ( EARLIER ( 'Dates'[Date] ) )
),
IF ( WEEKDAY ( [Date], 2 ) <= 5, 1 )
)


Which returns a colum that begins at 1 and goes up by 1 in each row.  Friday and Saturday get the same value and Sunday gets nothing.  It will reset back to 1 at each month.  

FD column w DOW.png
Is there a way to have this go in Descending order? I need one column to go from 1-X and another to go from X-1.  If I change the second column (FD2) to descending, they both change and nothing happens in the report. I hope that makes sense. 
FD&FD2.pngFD&FD2 report.png


Basically looking for a column that is "days left" from the first one we just created.  

Second question - In addition to filtering out for days of the week, is there a way to have the FD colum show the same value as the previous day when it hits a holiday? I have a holiday table I am just not sure how to add that filter to my existing DAX above. 

February:
Wednesday 16=12FD
Thursday 17 =13FD
Friday 18= 14
Saturday 19 =14
Monday 21 (Holiday) =14

Thank you in advance! 

2 ACCEPTED SOLUTIONS
vapid128
Solution Specialist
Solution Specialist

image.png

 

YYYYMM = YEAR([Date])*100+MONTH([Date])
 
Isholiday = [Date] in VALUES(TableHoliday[Holiday])
 
IsRank = not(WEEKDAY([Date],2) = 7 || WEEKDAY([Date],2) = 6 || [Isholiday])
 
FD2 = IF(WEEKDAY([Date],2)<>7,
RANKX(FILTER('Table','Table'[IsRank] && [YYYYMM]=EARLIER([YYYYMM])),[Date]),
BLANK()
)
 
 
FD = IF([FD2] = BLANK(),
BLANK(),
CALCULATE(MAX([FD2]),ALLEXCEPT('Table','Table'[YYYYMM])) -[FD2]+1
)
 
 
day = RANKX(FILTER('Table',[YYYYMM]=EARLIER('Table'[YYYYMM])),[Date],,ASC)

View solution in original post

vapid128
Solution Specialist
Solution Specialist

FD2=

var _FD2 = 

IF(WEEKDAY([Date],2)<>7,
RANKX(FILTER('Table','Table'[IsRank] && [YYYYMM]=EARLIER([YYYYMM])),[Date]),
BLANK()
)
 
return if(_FD2>0, _FD2-1,blank())

View solution in original post

11 REPLIES 11
KW123
Helper V
Helper V

@vapid128 

Thank you so much you have been a huge help!

KW123
Helper V
Helper V

Hi @vapid128 

I did try that earlier, however it returns a -1 for the Sunday as well when that should be blank.  I think I need an IF statement in this case? I am not sure, still very new to power bi! 

Days to go.png

 

vapid128
Solution Specialist
Solution Specialist

var _FD2 = 

IF(WEEKDAY([Date],2)<>7,
RANKX(FILTER('Table','Table'[IsRank] && [YYYYMM]=EARLIER([YYYYMM])),[Date]),
BLANK()
)
 
return if(_FD2>0, _FD2-1,blank())

@vapid128 

Thank you!!! It is saying that the Syntax for the return is incorrect? 

vapid128
Solution Specialist
Solution Specialist

Column =
var _FD2 = _______
RETURN IF(_FD2>0,_FD2-1,BLANK())

 

that should be no problem

@vapid128 

I am still not sure what I am doing incorrectly. Var_FD2.png

vapid128
Solution Specialist
Solution Specialist

FD2=

var _FD2 = 

IF(WEEKDAY([Date],2)<>7,
RANKX(FILTER('Table','Table'[IsRank] && [YYYYMM]=EARLIER([YYYYMM])),[Date]),
BLANK()
)
 
return if(_FD2>0, _FD2-1,blank())
vapid128
Solution Specialist
Solution Specialist

FD2 = IF(WEEKDAY([Date],2)<>7,
RANKX(FILTER('Table','Table'[IsRank] && [YYYYMM]=EARLIER([YYYYMM])),[Date]),
BLANK()
)
-1
KW123
Helper V
Helper V

@vapid128 

Thank you so so much!!! This is exactly what I was looking for. 

vapid128
Solution Specialist
Solution Specialist

image.png

 

YYYYMM = YEAR([Date])*100+MONTH([Date])
 
Isholiday = [Date] in VALUES(TableHoliday[Holiday])
 
IsRank = not(WEEKDAY([Date],2) = 7 || WEEKDAY([Date],2) = 6 || [Isholiday])
 
FD2 = IF(WEEKDAY([Date],2)<>7,
RANKX(FILTER('Table','Table'[IsRank] && [YYYYMM]=EARLIER([YYYYMM])),[Date]),
BLANK()
)
 
 
FD = IF([FD2] = BLANK(),
BLANK(),
CALCULATE(MAX([FD2]),ALLEXCEPT('Table','Table'[YYYYMM])) -[FD2]+1
)
 
 
day = RANKX(FILTER('Table',[YYYYMM]=EARLIER('Table'[YYYYMM])),[Date],,ASC)

Hi @vapid128 

Is there a way to have just the FD2 end in 0 instead of 1? 

FD2 = IF(WEEKDAY([Date],2)<>7,
RANKX(FILTER('Table','Table'[IsRank] && [YYYYMM]=EARLIER([YYYYMM])),[Date]),
BLANK()
)

So it would be -1 from the FD column

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors