topic Re: HELP ME! in DAX Commands and Tips

HELP ME!

TStan — Mon, 16 Oct 2023 06:06:41 GMT

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.

Re: HELP ME!

vicky_ — Tue, 17 Oct 2023 00:29:06 GMT

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.

Re: HELP ME!

TStan — Tue, 17 Oct 2023 02:35:18 GMT

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

Re: HELP ME!

Anonymous — Wed, 18 Oct 2023 03:36:27 GMT

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

Re: HELP ME!

TStan — Wed, 18 Oct 2023 05:10:20 GMT

Thank you so much - this works perfectly.

Best Regards,

Tim