Is there any way to get Last Year value with this kind of filter

[firstch]

Hi all please help me for dax to get last year value with this kind of filter 

this is my current dax

this time i have to use another field as slicer which data type is not number

Thankyou inadvance

Best Regard

firstch

[firstch]

hi @amitchandak 

if this reply is dupplication, im aplogize for it

i have try your solution and its says cant compare text with int

could you please try it with this sample data please:suppose this year is 2550/51

year	productionYear	fruit	value
2551	2550/51	apple	514823
2551	2550/51	guava	466683
2551	2550/51	grape	741446
2551	2550/51	apple	222318
2551	2550/51	guava	524553
2551	2550/51	grape	842539
2551	2550/51	apple	539855
2551	2550/51	guava	135614
2551	2550/51	grape	858769
2551	2550/51	apple	774507
2552	2551/52	guava	396773
2552	2551/52	grape	662128
2552	2551/52	apple	777911
2552	2551/52	guava	306057
2552	2551/52	grape	979162
2552	2551/52	apple	729226
2552	2552/53	guava	889772
2552	2552/53	grape	841883
2552	2552/53	apple	664174
2552	2552/53	guava	494220
2552	2552/53	grape	353886
2552	2552/53	apple	889931
2552	2552/53	guava	645981
2552	2552/53	grape	840849
2552	2552/53	apple	199105
2552	2552/53	guava	929611
2552	2552/53	grape	692048
2552	2552/53	apple	673923
2552	2552/53	guava	631041
2553	2552/53	grape	55413
2553	2552/53	apple	271165
2553	2552/53	guava	342101
2553	2552/53	grape	42158
2553	2552/53	apple	301398
2553	2552/53	guava	412220
2553	2552/53	grape	181670
2553	2552/53	apple	868329

best regard

firstch

[amitchandak · Accepted solution]

@firstch , what is the year here? In such a case I recommend using Rank on sortable column

Year Rank = RANKX(all('Date'),'Date'[Year Start date],,ASC,Dense)

Then have measure
This Year = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year Rank]=max('Date'[Year Rank])))
Last Year = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year Rank]=max('Date'[Year Rank])-1))

YTD

Column

Day of Year =datediff([Year Start date] , [Date],Day) +1

Measure

YTD= CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year Rank]=max('Date'[Year Rank]) && 'Date'[Day of Year] <= Max('Date'[Day of Year]) ))
LYTD = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year Rank]=max('Date'[Year Rank])-1 && 'Date'[Day of Year] <= Max('Date'[Day of Year])))

with month

YTD= CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year]) && 'Date'[Month] <= Max('Date'[Month]) ))
LYTD = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year])-1 && 'Date'[Month] <= Max('Date'[Month])))

Year and month are any numeric or sortable values , month needs to be number in the above case

refer custom time intelligence here

Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

[firstch]

hi @amitchandak thank you for you help

this is my sample data suppose year here is 2550/51

year	productionYear	fruit	value
2551	2550/51	apple	514823
2551	2550/51	guava	466683
2551	2550/51	grape	741446
2551	2550/51	apple	222318
2551	2550/51	guava	524553
2551	2550/51	grape	842539
2551	2550/51	apple	539855
2551	2550/51	guava	135614
2551	2550/51	grape	858769
2551	2550/51	apple	774507
2552	2551/52	guava	396773
2552	2551/52	grape	662128
2552	2551/52	apple	777911
2552	2551/52	guava	306057
2552	2551/52	grape	979162
2552	2551/52	apple	729226
2552	2552/53	guava	889772
2552	2552/53	grape	841883
2552	2552/53	apple	664174
2552	2552/53	guava	494220
2552	2552/53	grape	353886
2552	2552/53	apple	889931
2552	2552/53	guava	645981
2552	2552/53	grape	840849
2552	2552/53	apple	199105
2552	2552/53	guava	929611
2552	2552/53	grape	692048
2552	2552/53	apple	673923
2552	2552/53	guava	631041
2553	2552/53	grape	55413
2553	2552/53	apple	271165
2553	2552/53	guava	342101
2553	2552/53	grape	42158
2553	2552/53	apple	301398
2553	2552/53	guava	412220
2553	2552/53	grape	181670
2553	2552/53	apple	868329

best regard

firstch