cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## Max of table and not of record in table

Hi,

I am trying to create a measure that returns the volume of the current month. The current month in this example is (2021/05/01) - which corresponds to the max date of the table. See Table 1 below.
The following is the measure:

``````Current Month =
VAR MaxYear = YEAR(MAX('Table1'[Date]))
VAR MaxMonth = MONTH(MAX('Table1'[Date]))

RETURN
CALCULATE(SUM('Table1'[Volume]),'Table1'[Year]=MaxYear,'Table1'[Month]=MaxMonth)``````

However, when I add the measure to the table (See Table 2), it returns the volume for the current month that corresponds to the max date for that customer and not the max date for the table - which can be seen by "Company 3" in Table 2, the date corresponds to (2020/01/01) as opposed ti (2021/05/01). I do not want to hard code the date and year as that would mean I need to update the measure every month. How can I fix the value such that it returns the max date of the table column and does not change the context based on the record its applied to?

Table1

 Customer Date Year Month Volume Company 1 2019/05/01 2019 5 22 Company 1 2020/05/01 2020 5 3 Company 1 2021/05/01 2021 5 4 Company 2 2020/01/01 2020 1 19 Company 2 2021/05/01 2021 5 26 Company 3 2020/01/01 2020 1 7

Table2

 Customer Current Month Company 1 4 Company 2 26 Company 3 7

Thank you

1 ACCEPTED SOLUTION
Super User

@Anonymous , If you want customer wise max, Try like

Current Month =
VAR MaxYear = YEAR(MAX('Table1'[Date]))
VAR MaxMonth = MONTH(MAX('Table1'[Date]))

RETURN
CALCULATE(SUMX(values('Table'[Customer]),calculate(sum('Table1'[Volume]))),filter('Table','Table1'[Year]=MaxYear && 'Table1'[Month]=MaxMonth))

Max based on table

Current Month =
VAR MaxYear = YEAR(MAXX(allselected('Table1'), 'Table'[Date]))
VAR MaxMonth = MONTH(MAXX(allselected('Table1'), 'Table'[Date]))

RETURN
CALCULATE(SUM('Table1'[Volume]),,filter('Table','Table1'[Year]=MaxYear && 'Table1'[Month]=MaxMonth))

Today ----
Current Month =
VAR MaxYear = YEAR(today())
VAR MaxMonth = MONTH(today())

RETURN
CALCULATE(SUM('Table1'[Volume]),'Table1'[Year]=MaxYear,'Table1'[Month]=MaxMonth)

2 REPLIES 2
Super User

@Anonymous , If you want customer wise max, Try like

Current Month =
VAR MaxYear = YEAR(MAX('Table1'[Date]))
VAR MaxMonth = MONTH(MAX('Table1'[Date]))

RETURN
CALCULATE(SUMX(values('Table'[Customer]),calculate(sum('Table1'[Volume]))),filter('Table','Table1'[Year]=MaxYear && 'Table1'[Month]=MaxMonth))

Max based on table

Current Month =
VAR MaxYear = YEAR(MAXX(allselected('Table1'), 'Table'[Date]))
VAR MaxMonth = MONTH(MAXX(allselected('Table1'), 'Table'[Date]))

RETURN
CALCULATE(SUM('Table1'[Volume]),,filter('Table','Table1'[Year]=MaxYear && 'Table1'[Month]=MaxMonth))

Today ----
Current Month =
VAR MaxYear = YEAR(today())
VAR MaxMonth = MONTH(today())

RETURN
CALCULATE(SUM('Table1'[Volume]),'Table1'[Year]=MaxYear,'Table1'[Month]=MaxMonth)

Anonymous
Not applicable

Thank! I used max based on table.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.