Forum Discussion

Fcoatis's avatar
Fcoatis
Post Patron
9 years ago
Solved

Next two weeks visits

In a simple table I have Dates in one column and Names in the second one. I need to calculate number of visits in the next 2 weeks from the max date in the filter context. Any help?

 

Thanks in advance

 

Need Help
  • Fcoatis's avatar
    Fcoatis
    9 years ago

    This seems to resolve:

     

    Next2Wks = 
var
	max_Date = CALCULATE(MAX('Calendar'[Date]);ALLSELECTED('Calendar'[Date]))
RETURN

CALCULATE(COUNTA(tVisitas[Nome]);FILTER(all('Calendar');'Calendar'[Date]>=max_Date&&'Calendar'[Date]<=max_Date+16))

    Thank you 

4 Replies

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Microsoft Employee
    9 years ago

    Hi Fcoatis,

    The filter context is a slicer, right? If it is, you need to create a measure to get the max date of the slicer. Please use the formula below.

    max_date=CALCULATED(MAX(filtertable[date]),ALLSELECTED(filtertable[date]))


    Then create a measure using the formula.

    the number of visits=CALCULATE(COUNTA(Table[name]),FILTER(Table,Table[Date]>=[max_date]&&Table[Date]<=DATEADD([max_date],14,day))


    Best Regards,
    Angelia

      • Fcoatis's avatar
        Fcoatis
        Post Patron
        9 years ago

        This seems to resolve:

         

        Next2Wks = 
var
	max_Date = CALCULATE(MAX('Calendar'[Date]);ALLSELECTED('Calendar'[Date]))
RETURN

CALCULATE(COUNTA(tVisitas[Nome]);FILTER(all('Calendar');'Calendar'[Date]>=max_Date&&'Calendar'[Date]<=max_Date+16))

        Thank you