Count same values in most recent date

[Anonymous]

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.  

Date	Aspect	Value
January 2020	Responsibility	1
January 2020	Validity	0
January 2020	Satefy	-1
January  2020	Guarantee	-1
February 2020	Responsibility	0
February 2020	Validity	-1
February 2020	Satefy	0
February 2020	Guarantee	0
March 2020	Responsibility	-1
March 2020	Validity	-1
March 2020	Satefy	-1
March 2020	Guarantee	1

[Vera_33 · Accepted solution]

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

[amitchandak]

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

[Vera_33 · Accepted solution]

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]

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.  

Date	Aspect	Value	Location
1 januari 2020	Responsibility	1	The Netherlands
1 januari 2020	Validity	0	The Netherlands
1 januari 2020	Satefy	-1	The Netherlands
1 januari 2020	Guarantee	-1	The Netherlands
1 februari 2020	Responsibility	0	The Netherlands
1 februari 2020	Validity	-1	The Netherlands
1 februari 2020	Satefy	0	The Netherlands
1 februari 2020	Guarantee	0	The Netherlands
1 maart 2020	Responsibility	-1	The Netherlands
1 maart 2020	Validity	-1	The Netherlands
1 maart 2020	Satefy	-1	The Netherlands
1 maart 2020	Guarantee	1	The Netherlands
1 januari 2019	Responsibility	1	Germany
1 januari 2019	Validity	0	Germany
1 januari 2019	Satefy	-1	Germany
1 januari 2019	Guarantee	-1	Germany
1 februari 2019	Responsibility	0	Germany
1 februari 2019	Validity	-1	Germany
1 februari 2019	Satefy	0	Germany
1 februari 2019	Guarantee	0	Germany
1 maart 2019	Responsibility	-1	Germany
1 maart 2019	Validity	-1	Germany
1 maart 2019	Satefy	-1	Germany
1 maart 2019	Guarantee	1	Germany
1 mei 2019	Responsibility	1	Spain
1 mei 2019	Validity	0	Spain
1 mei 2019	Satefy	-1	Spain
1 mei 2019	Guarantee	-1	Spain
1 juni 2019	Responsibility	0	Spain
1 juni 2019	Validity	-1	Spain
1 juni 2019	Satefy	0	Spain
1 juni 2019	Guarantee	0	Spain
1 oktober 2019	Responsibility	-1	Spain
1 oktober 2019	Validity	-1	Spain
1 oktober 2019	Satefy	-1	Spain
1 oktober 2019	Guarantee	1	Spain

[Vera_33 · Accepted solution]

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

[Anonymous]

Hi @Vera_33 ,

At first, I thought the formula was working. But when I applied it on the whole data set, I discovered an error. 

There are 4 locations who all happen to appear with the value '-1' showing up once in the most date. When there are 82 of the 86 locations are selected which do not have that value in the most recent date, the measure shows nothing, so that is correct. But when there is one of these 4 locations selected with all the other locations, the measure shows '83'. When all locations are selected, the measure shows 86. So, something goes wrong, and I can't figure out what that is. I hope you could help me with this. Here is a link to the complete file:

https://drive.google.com/file/d/14PfV1B2Pd4KbXsDAB73u7o-_zSRnTys2/view?usp=sharing

[Vera_33]

Hi @Anonymous ,

I am happy to help, but I am not able to get your file...Google is not available in my region. Is there any other way you can share it? Say SharePoint? Email?

[Anonymous]

Hi @Vera_33 ,

Sure! 

https://1drv.ms/x/s!AgdRVF_X5GcAjnzbOoC_Eg1E02ZF?e=pDpP4f

[Vera_33]

@Anonymous can't access it, either:( 

my email (hotmail): vera-33

[Anonymous]

Hi @Vera_33 ,

I sent you an email. I hope to the right adress.

[Vera_33]

yes, it is correct one, @Anonymous  have you received my email?

[Anonymous]

Hi @Vera_33 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))