How to get the number from the third case per year

[umekuro]

Hi,

I have the fact data above.
I do not know the measure to get the ExpectedValue column of the table below.
The condition is that I want to display only the number of cases that exceed 3 per year.
Can anyone give me some advice on how to do this?

Thank you in advance.

Date	Value
2021/4/3	1
2021/7/10	1
2021/7/22	2
2021/9/3	1
2021/9/9	1
2021/11/10	1

[PC2790]

Can you elaborate on your requirement?

> Do you need a new column in the existing table?

> Do you need the number of cases per year as per month or the third case per year?

[umekuro]

Dear PC2790,

Thank you for your advice.
I want to represent the numbers in the red box with a table visual like the one below.
And it needs to be a measure.
For your second question,

I want to get value counted from the third case, not three cases per month, but from the third case cumulatively by year.
In the data above,
1 in April, but it haven't reached 3 for the year yet, Not counted.
3 in July, count the two cases after reaching three cases for the year,

and the months after that,

the number of cases has reached 3.
All will be counted.

I hope this helps.

[PaulDBrown]

Assuming you have a number value for YearMonth, Try:

sum>2=

CALCULATE(SUM(Table[Value]),

FILTER(ALL(Table), Table[YearMonthNum]<=MAX(Table[YearMonthNum])))

Now add the [sum>2] measure to the filter pane and set the value to greater or equal to 3

If you are using a date table, use:

sum>2=

CALCULATE(SUM(Table[Value]),

FILTER(ALL(DateTable), DateTable[Date]<=MAX(DateTable[Date])))

[umekuro]

Dear PaulDBrown,

Thank you for your advice. 

Sorry for my lack of explanation.
The matrix visual I want to create cannot use the filter pane because I want to include other measures in one Matrix Visual.
The value below and now the expected value and other columns will also be displayed in one matrix.
Is there any solution to this problem?

I would like to add that I use the date table.

BR

[PaulDBrown]

In that case,  try

sum>2=
VAR _ calc =

CALCULATE(SUM(Table[Value]),

FILTER(ALL(DateTable), DateTable[Date]<=MAX(DateTable[Date])))
RETURN

IF(_calc >2, SUM(Table[Value]))

[umekuro]

Thank you,

I tried your advice,

Unfortunately, we did not get the numbers we were looking for.
We want the numbers to be in the red.
Sorry if my explanation is not good enough.

[PaulDBrown]

Why is the first Value = 2, if the column value is 3?

to get the correct totals change the measure to:

sum>2=
SUMX(Date Table, 

CALCULATE(IF(

CALCULATE(SUM(Table[Value]),

FILTER(ALL(DateTable), DateTable[Date]<=MAX(DateTable[Date]))) > 2, SUM(Table[Value]))
)

[umekuro]

Sorry for the confusion.
value is the number of cases.
It does not count up to two per year.
I want to count the third case as the first, so
One in April 2021 and the first of three in July 2021.
The count will be disqualified and there will be two cases in July.
Is that an explanation for your Measure?

[PaulDBrown · Accepted solution]

Ok, I think this is what you are after:

values after 2 cases =

VAR threshold = 

CALCULATE(SUM(Table[Value]),

FILTER(ALL(DateTable), DateTable[Date]<=MAX(DateTable[Date])))

VAR Countfrom =
MINX(FILTER(Date table, threshold = 3), Date[Date])

RETURN

CALCULATE(SUM(Table[Value]), FILTER(Date Table, Date Table[Date]>= Countfrom))

[umekuro]

Thank you so much.

I'll try it!