Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Count same values in most recent date

Hi everyone,   I want to create a formula which count how many times a specific value shows up in the most recent date. I made a sample set (see below). I want to know how many times '-1' shows up...
  • Anonymous's avatar
    Anonymous
    6 years ago

    It depends on how you want to visualize it, if you just want to hard coded like this, or maybe you want a matrix, so you will have dimTable to show all the Value, then you get the CurValue=SELECTEDVALUE(dimTable[Value]), or you have a Date Table, then you need to take care the relationship as well.

     

    CountValue=
    VAR MaxMonth = MONTH(MAX(yourTable[Date]))
    VAR CurValue = -1
    RETURN
    COUNTROWS(FILTER(yourTable,MONTH(yourTable[Date])=MaxMonth && yourTable[Value] = CurValue))

  • Anonymous's avatar
    Anonymous
    6 years ago

    If you just want 9, do this, but you need to take care of your date format

    CountValue1=

    VAR MaxYear = YEAR(MAX(yourTable[Date]))

    VAR MaxMonth = MONTH(MAX(yourTable[Date]))

    VAR CurValue = -1

    RETURN

    SUMX(

         GROUPBY(yourTable,[Location]),

         COUNTROWS(FILTER(yourTable,Year(yourTable[Date])=MaxYear && MONTH(yourTable[Date])=MaxMonth && yourTable[Value] = CurValue)))