Filter for largest week for the most recent Year

[knajar]

Hello,

I am trying to create a measure which I can use to filer to create a table that only includes rows for the latest(largest) week for the most recent year. 

For example, the correct output below should be the first three rows having latest week column as 1 and the rest as 0.

The calculated column is what outputs using the following code:

Latest Week =

VAR LatestYR =

CALCULATE ( MAX ( 'Weekly Estimating Lead-Time Report'[Year] ), ALL ( 'Weekly Estimating Lead-Time Report') )

VAR LatestWK =

CALCULATE ( MAX ( 'Weekly Estimating Lead-Time Report'[Week No.] ), FILTER('Weekly Estimating Lead-Time Report','Weekly Estimating Lead-Time Report'[Year]=LatestYR))

RETURN

IF ( MIN('Weekly Estimating Lead-Time Report'[Week No.])= LatestWK , 1, 0 )

Thank you very much for your help.

[Whitewater100]

Hi:

The other members are giving you a table for the records for the latest week. What are you missing?

The measure for latest week could be

Latest Week = 
var WeekNo = SELECTEDVALUE(Dates[Week No])
 return
IF(WeekNo =MAX(Dates[Week No], 1, "-")

This is another table function to give you the same type of table:

Calc Tble = CALCULATETABLE('YourTable',

Dates[Year]= MAX(Dates[Year]) &&

Dates[Week of Year] = MAX(Dates[Week No.])

The measure for latest week could be

Latest Week = 
var WeekNo = SELECTEDVALUE(Dates[Week No])
 return
IF(WeekNo =MAX(Dates[Week No], 1, "-")

[littlemojopuppy]

@Whitewater100 right???  I provided the DAX and a screen snip showing exactly the intended outcome...

[littlemojopuppy]

Hi @knajar 

This will create a table that contains the only the records for the top week in the most recent year

Calculated Table = 
VAR	MostRecentYear =
	CALCULATE(
		MAX(RawData[Year]),
		ALL(RawData)
	)
VAR	LargestWeek =
	TOPN(
		1,
		FILTER(
			ALL(RawData),
			RawData[Year] = MostRecentYear
		),
		RawData[WeekNumber],
		DESC
	)
RETURN

LargestWeek

Hope this helps!

[knajar]

Hello,

Thank you for the reply. When I tried this solution I get the following error:

"The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."

[littlemojopuppy · Accepted solution]

@knajar how's this look?

[knajar]

Thank you very much for the response. It worked.

[littlemojopuppy]

@knajar glad I could help!