Forum Discussion

IF's avatar
IF
Icon for Post Prodigy rankPost Prodigy
5 years ago
Solved

Previous year December value

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

a12.20191.12.201912
b12.20191.12.201912
a01.201901.1.201945
b01.201901.1.201945
a12.20181.12.201878
b12.20181.12.201878
a02.20191.2.20191122
b02.20191.2.20191122
a12.20171.12.20171214
b12.20171.12.20171315
a03.20171.03.20171820
b03.20171.03.20171921
a12.20201.12.20203133
b12.20201.12.20203234
a04.20201.04.20203739
b04.20201.04.20203840
a12.20191.12.20194547
b12.20191.12.20194648

 

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!

 

Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    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.