filter dataset for only last 20 working days in DAX

[chefe]

Hi there,

I am trying to to write e measure that returns a simple count. The only catch is that it should filter the underlying dataset to only the last 20 working days. For this I have a proper date table ("Dates") which recognises working days with the field [IS_WORKING_DAY]. 

I successfully tested the formula via creating a custom table with it, please see the following screenshot. 

T = 
VAR tbl_filtered_is_working_day = FILTER(Dates;Dates[IS_WORKING_DAY]=1)
VAR tbl_filtered_ranked = ADDCOLUMNS(SUMMARIZE(tbl_filtered_is_working_day;Dates[DATE_KEY]);"Rank";RANKX(tbl_filtered_is_working_day;Dates[DATE_KEY])) 
VAR tbl_filtered_top20 = FILTER(tbl_filtered_ranked;[Rank]<=20)
VAR tbl_last_20days = SUMMARIZE(tbl_filtered_top20;Dates[DATE_KEY])

RETURN tbl_last_20days

Only the last 20 working days are returned:

Success so far. However, when I try to incorporate this into my measure, I does not apply the filter correctly. The measure formula is as follows, also please the the following screenshot which displays the result visually.

Count of last 20 days' CDS spreads TEST = 

VAR tbl_filtered_is_working_day = FILTER(Dates;Dates[IS_WORKING_DAY]=1)
VAR tbl_filtered_ranked = ADDCOLUMNS(SUMMARIZE(tbl_filtered_is_working_day;Dates[DATE_KEY]);"Rank";RANKX(tbl_filtered_is_working_day;Dates[DATE_KEY])) 
VAR tbl_filtered_top20 = FILTER(tbl_filtered_ranked;[Rank]<=20)
VAR tbl_last_20days = SUMMARIZE(tbl_filtered_top20;Dates[DATE_KEY])

RETURN IF(HASONEVALUE(Dates[DATE_KEY])
	;CALCULATE([Count of CDS spreads]
		;FILTER(Dates;Dates[DATE_KEY]>=FIRSTDATE(tbl_last_20days))
	)
	;1 //for row total, dummy = 1 for now
)

More results are returned than expected. Does anyone know why? The beginning formula is identical.

Any help is greatly appreciated!

Cheers

[v-haibl-msft]

@chefe

As I understand it, you only want to show the last 20 days in the Table chart. It seems that you select Dates[DATE_KEY] for Values of Table chart. If so, all dates will be shown in the Table chart. You can try to select T[DATE_Key] for Values of Table chart.

If you still cannot get your problem resolved, could you please also provide the measure formula of [Count of CDS spreads]?

Best Regards,

Herbert

[jahida · Accepted solution]

Guessing it's because the very first FILTER is only working on the Dates in the current context, which means it will pull 20 days back from any starting date. Try changing the first line to:

FILTER(ALL(Dates); Dates[IS_WORKING_DAY]=1)

might not work but worth a shot.

[chefe]

@jahida Thanks that worked. Nice.

Though I still need to wrap my head around this (even though you already provided explanation) 😕

I'm still new @ "thinking DAX"