Forum Discussion

TStan's avatar
TStan
Frequent Visitor
2 years ago
Solved

HELP ME!

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 t...
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

vicky_'s avatar
vicky_
Icon for Super User rankSuper User
2 years ago

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's avatar
    TStan
    Frequent Visitor
    2 years ago

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