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

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
    )