Filtering out for Holidays & Descending order

[KW123]

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! 

[vapid128 · Accepted solution]

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)

[KW123]

@vapid128 

Thank you so much you have been a huge help!

[KW123]

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! 

[vapid128]

var _FD2 = 

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

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

BLANK()

)

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

[KW123]

@vapid128 

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

[vapid128]

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

that should be no problem

[KW123]

@vapid128 

I am still not sure what I am doing incorrectly. 

[vapid128 · Accepted solution]

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]

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

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

BLANK()

)

-1

[KW123]

@vapid128 

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

[vapid128 · Accepted solution]

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)

[KW123]

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