Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Counting values based on two filters with 'Today' variable

Hello all,

 

I have created the following measure, which nicely returns the number of rows in my table where the 'Current Due Date' is in the past. However, I want to add a second filter to this so that it returns the number of rows where the due date is in the past, but not older than 5 days.

However, DAX won't led me add a second filter. Anyone know how to fix this?

 

Risks Next 5 Days = 

VAR __Today = Today()

RETURN

COUNTROWS(

      FILTER(

            ALL(Table),Table[Current Due Date] <__Today
      )

)

Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi  Anonymous ,

    Here are the steps you can follow：

    1. Create measure.

    Rank =
var _today=TODAY()
return
RANKX(FILTER(ALL('Table'),'Table'[Current Due Date]<=_today),CALCULATE(MAX([Current Due Date])),,DESC)
    Flag =
IF(
    [Rank]>1&& [Rank]<=6,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

  • Syk's avatar
    Syk
    Resident Rockstar
    4 years ago

    Try something like this

    Risks Next 5 Days = 
VAR __Today = today()
VAR __Todayminusfive = dateadd(today(),-5,days)
RETURN
CALCULATE(
	COUNTROWS('Table')
	,DATESBETWEEN(Table[Current Due Date],__Todayminusfive,__Today)
	)
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thank you - but this throws up an error: The 'Today()' in the second variable is not allowed because a column is required. If it helps, here is the table:

       

      SiteRisk IDRisk DetailCurrent Due Date
      Site #1Risk ID #1Risk Detail #120/11/2022
      Site #2Risk ID #2Risk Detail #225/01/2022
      Site #3Risk ID #3Risk Detail #301/09/2022
      Site #4Risk ID #4Risk Detail #405/09/2022
      Site #5Risk ID #5Risk Detail #508/02/2022
      Site #6Risk ID #6Risk Detail #620/08/2022
      Site #7Risk ID #7Risk Detail #703/10/2022
      Site #8Risk ID #8Risk Detail #806/07/2022
      Site #9Risk ID #9Risk Detail #904/05/2022
      Site #10Risk ID #10Risk Detail #1030/03/2022
      Site #11Risk ID #11Risk Detail #1111/05/2022
      Site #12Risk ID #12Risk Detail #1204/05/2022
      Site #13Risk ID #13Risk Detail #1312/05/2022
      Site #14Risk ID #14Risk Detail #1424/01/2022
      Site #15Risk ID #15Risk Detail #1505/04/2022
      Site #16Risk ID #16Risk Detail #1627/03/2022
      Site #17Risk ID #17Risk Detail #1709/11/2022
      Site #18Risk ID #18Risk Detail #1827/06/2022
      Site #19Risk ID #19Risk Detail #1915/09/2022
      Site #20Risk ID #20Risk Detail #2020/11/2022
      • Syk's avatar
        Syk
        Resident Rockstar
        4 years ago

        That's helpful!  In the screenshot I used 150 days because nothing was within 5 days but try this

        Measure = 
SUMX('Table',
    var datedifference = DATEDIFF('Table'[Current Due Date],TODAY(),DAY)
    var result = if(datedifference >= 0 && datedifference <= 5,1)
    return result
    )



         

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi  Anonymous ,

    Here are the steps you can follow：

    1. Create measure.

    Rank =
var _today=TODAY()
return
RANKX(FILTER(ALL('Table'),'Table'[Current Due Date]<=_today),CALCULATE(MAX([Current Due Date])),,DESC)
    Flag =
IF(
    [Rank]>1&& [Rank]<=6,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