HELP ME!

[TStan]

Hi there,

I need some DAX code which matches an upload date (today's date) in my fact table to the Date column in my Date Table. I then need a column which returns True if the date falls within the previous 7 working days and false if it does not. 

I intend to then add that as a filter to my visual to get a dynamic 7 day rolling result.

Thanks in advance.

[v-yangliu-msft · Accepted solution]

Hi  @TStan ,

Here are the steps you can follow：

1. Create measure.

Flag =
var _if=
IF(
    WEEKDAY(MAX('Fact Table'[Date]),2) in {6,7} || MAX('Fact Table'[Date])>TODAY(), BLANK(),
RANKX(
    FILTER(ALLSELECTED('Fact Table'),
    NOT(WEEKDAY('Fact Table'[Date],2)) in {6,7} &&'Fact Table'[Date]<=TODAY() ),CALCULATE(MAX('Fact Table'[Date])),,DESC))
return
IF(
    _if>1&&_if<=8&&_if<>BLANK(),1,0)

2. Place [Flag]in Filters, set is=1, apply filter.

3. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[TStan]

Thank you so much - this works perfectly.

Best Regards,

Tim

[vicky_]

Consider using the NETWORKDAYS function to find the previous 7 working days:

dateInLast7WorkingDays =
var a = CALCULATE(NETWORKDAYS(SELECTEDVALUE('Table'[Date]), TODAY()), KEEPFILTERS('Table'[Date])) 
return COUNTROWS(FILTER('Table', a > 0 && a <= 8))

You can pass in a list of holidays to the NETWORKDAYS() function for more accurate results. 

[TStan]

That works however I need it to exclude Saturday/Sundays and return the last 7 working days. Any ideas?