Calculation Excluding Months if date for the upcoming Month is not present

[katyfailoo]

Hi All,

I am struggling with a calculation. Hoping to get some guidance around this

I have a matrix as seen below that pulls from the following tables: Calendar and Employees. Columns used are

• Calendar - Month
• Calendar - Year
• Employees - Employee Count (Measure)
• Employee - Category

	Month
Year	January	February	March	April	May	June	July
2018	10	8	6	10	12	10	10
Category A	5	4	3	5	6	5	5
Category B	5	4	3	5	6	5	5
2019	20	30	40	50	40	20	20
2020	20	30	40	50	70	10	10
2021	20	30	40	20

Question, how can I add all the values for all the months and years from January - April. However I don't want to include May- July because I don't have the data for 2021 for those months. If data is not found for the current year and that month do not add those values (exclude those months for the previous years too).

I tried using a a flag to flag the months that are less than the current month, but it didn't seem to work.

Your help and guidance is appreciated.

Thank you!

[v-jingzhang · Accepted solution]

Hi @katyfailoo 

I think you original idea to use a flag is doable. You could add a calculated column to the Employee table with below code. 

flag =
VAR vMaxMonth = MONTH ( MAX ( 'Employee'[Date] ) )
RETURN
    IF ( MONTH ( 'Employee'[Date] ) <= vMaxMonth, 1, 0 )

Then create a measure

Employee Count New = CALCULATE ( [Employee Count], 'Employee'[flag] = 1 )

Use this new measure in the matrix. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[amitchandak]

@katyfailoo , Assume you are using a measure , M1

Then try a measure like

M1 New =

var _max = maxx(allselcted(employee_, employee[Date])

return

calculate([M1], filter(calendar, Calendar[Date] <=_max))

[katyfailoo]

Thank you so much for the speedy response. It did not work for me. It doesn't filter out May, June and July. Is there another way I can get to filter out the sum of May June and July for years (2018, 2019, and 2020) because 2021 does not have any values in those months?