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 in the latest month (March). So the answer should be 3.  

DateAspectValue
January 2020Responsibility1
January 2020Validity0
January 2020Satefy-1
January  2020Guarantee -1
February 2020Responsibility0
February 2020Validity-1
February 2020Satefy0
February 2020Guarantee 0
March 2020Responsibility-1
March 2020Validity-1
March 2020Satefy-1
March 2020Guarantee 1
Need Help
  • 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)))

11 Replies

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

      Hi Anonymous and amitchandak ,

       

      Thank you both for your help. 

      The formula of you did work, Vera. My orginal dataset contains data over several years. So I discovered that your formula counts values of e.g. December 2017 when the latest date actually December 2019 was. To work around that, the formula will be as follows:

       

      CountValue=

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

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      What if you want to make a distinction on 'Location', so that the formula has to return all values with '-1' in the latest month and year for each individual location. So, the latest month and year for The Netherlands is March 2020, for Germany March 2019 and for Spain Oktober 2019. I copied/paste the values, so formula should return 9.  

       

      DateAspectValueLocation
      1 januari 2020Responsibility1The Netherlands
      1 januari 2020Validity0The Netherlands
      1 januari 2020Satefy-1The Netherlands
      1 januari 2020Guarantee -1The Netherlands
      1 februari 2020Responsibility0The Netherlands
      1 februari 2020Validity-1The Netherlands
      1 februari 2020Satefy0The Netherlands
      1 februari 2020Guarantee 0The Netherlands
      1 maart 2020Responsibility-1The Netherlands
      1 maart 2020Validity-1The Netherlands
      1 maart 2020Satefy-1The Netherlands
      1 maart 2020Guarantee 1The Netherlands
      1 januari 2019Responsibility1Germany
      1 januari 2019Validity0Germany
      1 januari 2019Satefy-1Germany
      1 januari 2019Guarantee -1Germany
      1 februari 2019Responsibility0Germany
      1 februari 2019Validity-1Germany
      1 februari 2019Satefy0Germany
      1 februari 2019Guarantee 0Germany
      1 maart 2019Responsibility-1Germany
      1 maart 2019Validity-1Germany
      1 maart 2019Satefy-1Germany
      1 maart 2019Guarantee 1Germany
      1 mei 2019Responsibility1Spain
      1 mei 2019Validity0Spain
      1 mei 2019Satefy-1Spain
      1 mei 2019Guarantee -1Spain
      1 juni 2019Responsibility0Spain
      1 juni 2019Validity-1Spain
      1 juni 2019Satefy0Spain
      1 juni 2019Guarantee 0Spain
      1 oktober 2019Responsibility-1Spain
      1 oktober 2019Validity-1Spain
      1 oktober 2019Satefy-1Spain
      1 oktober 2019Guarantee 1Spain

       

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

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    measure =
    var _sel = -1
    return
    calculate(count(table[Value]),Table[date] = format(today(),"MMMM YYYY"),table[value] =_sel)