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 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.

  • 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

4 Replies

  • vicky_'s avatar
    vicky_
    Super 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?

  • Anonymous's avatar
    Anonymous
    Not applicable
    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

    • TStan's avatar
      TStan
      Frequent Visitor
      2 years ago

      Thank you so much - this works perfectly.

      Best Regards,

      Tim