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

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.

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
Solution Specialist

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)
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())
11 REPLIES 11
Helper V

@vapid128

Thank you so much you have been a huge help!

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!

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

@vapid128

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

Solution Specialist

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

that should be no problem

Helper V

@vapid128

I am still not sure what I am doing incorrectly.

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())
Solution Specialist
FD2 = IF(WEEKDAY([Date],2)<>7,
RANKX(FILTER('Table','Table'[IsRank] && [YYYYMM]=EARLIER([YYYYMM])),[Date]),
BLANK()
)
-1
Helper V

@vapid128

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

Solution Specialist

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)
Helper V

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

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.