Previous year December value

[IF]

Hi

I want to show the value of December previous year. I followed the steps that is posted but couldn't get the result.

here is the measure:

MeasureValue1 = CALCULATE(SUM('Table'[value1]),FILTER('Table', 'Table'[Year]=SELECTEDVALUE('Table'[Year])-1&&'Table'[Month]=12))

Second measure:

MeasureValue1(b) =

VAR _selectedyear=SELECTEDVALUE('Table'[Date])-1

return CALCULATE(SUM('Table'[value1]), FILTER(ALL('Table'), YEAR('Table'[Date])=_selectedyear&&MONTH('Table'[Date])=12))

both doesn't give me result.

NameMonth.YearDatevalue1value2

a	12.2019	1.12.2019	1	2
b	12.2019	1.12.2019	1	2
a	01.2019	01.1.2019	4	5
b	01.2019	01.1.2019	4	5
a	12.2018	1.12.2018	7	8
b	12.2018	1.12.2018	7	8
a	02.2019	1.2.2019	11	22
b	02.2019	1.2.2019	11	22
a	12.2017	1.12.2017	12	14
b	12.2017	1.12.2017	13	15
a	03.2017	1.03.2017	18	20
b	03.2017	1.03.2017	19	21
a	12.2020	1.12.2020	31	33
b	12.2020	1.12.2020	32	34
a	04.2020	1.04.2020	37	39
b	04.2020	1.04.2020	38	40
a	12.2019	1.12.2019	45	47
b	12.2019	1.12.2019	46	48

If anymonth in 2020 is selected the result should be 93, for selection of 2019, it should be 14; and it should be 25 for selection of any value in 2018.

the file is available at: https://www.dropbox.com/s/g6cs2tsbk9x6s0k/December%20previous%20year%28share%29.pbix?dl=0

Thanks in advance!

[ryan_mayu · Accepted solution]

@IF 

you can try this

Measure = 
VAR yr=right(SELECTEDVALUE('Table'[Month.Year]),4)-1
return SUMX(FILTER(all('Table'),'Table'[Month.Year]="12."&yr),'Table'[value1])

please kindly check the below pbix file.

[amitchandak]

@IF , with help from date table

last Dec =

var _mon = month(today())

return

CALCULATE(SUM(Table[Value]),DATESMTD(dateadd('Date'[Date],-1*_mon ,MONTH)))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.