topic Re: Filtering out for Holidays & Descending order in DAX Commands and Tips

Filtering out for Holidays & Descending order

KW123 — Mon, 18 Jul 2022 17:32:40 GMT

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!

Re: Filtering out for Holidays & Descending order

vapid128 — Mon, 18 Jul 2022 23:16:12 GMT

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)

Re: Filtering out for Holidays & Descending order

KW123 — Tue, 19 Jul 2022 18:03:48 GMT

vapid128

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

Re: Filtering out for Holidays & Descending order

KW123 — Wed, 20 Jul 2022 16:21:58 GMT

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

Re: Filtering out for Holidays & Descending order

vapid128 — Wed, 20 Jul 2022 17:39:22 GMT

FD2 = IF(WEEKDAY([Date],2)<>7,

RANKX(FILTER('Table','Table'[IsRank] && [YYYYMM]=EARLIER([YYYYMM])),[Date]),

BLANK()

)

-1

Re: Filtering out for Holidays & Descending order

KW123 — Wed, 20 Jul 2022 17:58:07 GMT

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!

Re: Filtering out for Holidays & Descending order

vapid128 — Wed, 20 Jul 2022 17:59:54 GMT

var _FD2 =

IF(WEEKDAY([Date],2)<>7,

RANKX(FILTER('Table','Table'[IsRank] && [YYYYMM]=EARLIER([YYYYMM])),[Date]),

BLANK()

)

return if(_FD2>0, _FD2-1,blank())

Re: Filtering out for Holidays & Descending order

KW123 — Wed, 20 Jul 2022 18:18:21 GMT

vapid128

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

Re: Filtering out for Holidays & Descending order

vapid128 — Wed, 20 Jul 2022 18:24:36 GMT

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

that should be no problem

Re: Filtering out for Holidays & Descending order

KW123 — Wed, 20 Jul 2022 18:50:25 GMT

vapid128

I am still not sure what I am doing incorrectly.

Re: Filtering out for Holidays & Descending order

vapid128 — Wed, 20 Jul 2022 23:53:40 GMT

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

Re: Filtering out for Holidays & Descending order

KW123 — Thu, 21 Jul 2022 15:05:55 GMT

vapid128

Thank you so much you have been a huge help!