How Can i do this "For Each Year I need to get the previous Year Values"

[Anonymous]

Hello All,

I need help with this scenario

I have a Table

KPI_ID	Program_ID	Date	Values
KP1	P1	01-01-2017	23
KP2	P1	01-01-2018	25
KP3	P2	01-01-2017	15
KP4	P2	01-01-2018	45
KP5	P2	01-01-2019	85
KP6	P3	01-01-2017	65
KP7	P3	01-01-2018	25
KP8	P4	01-01-2017	45
KP9	P4	01-01-2017	10

now i have to generate a line chart comparing previous year and the next year values

like this

KPI_ID	Program_ID	Date	Values	previous Date	previous Values
KP1	P1	01-01-2017	23
KP2	P1	01-01-2018	25	01-01-2017	23
KP3	P2	01-01-2017	15
KP4	P2	01-01-2018	45	01-01-2017	15
KP5	P2	01-01-2019	85	01-01-2018	45
KP6	P3	01-01-2017	65
KP7	P3	01-01-2018	25	01-01-2017	65
KP8	P4	01-01-2017	45
KP9	P4	01-01-2018	10	01-01-2017	45

[harshnathani]

Hi @Anonymous ,

Please create 2 Calculated Columns

Previous Date = CALCULATE(

MAX(Table1[Date]),FILTER(ALLEXCEPT(Table1,Table1[Program_ID]), Table1[Date] < EARLIER(Table1[Date])))

Previous Values = CALCULATE(

MAX(Table1[Values]),FILTER(ALLEXCEPT(Table1,Table1[Program_ID]), Table1[Date] < EARLIER(Table1[Date])))

Regards,

Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!

[Anonymous]

Hello there,

Thanks for the reponds

the only issue i am getting is the SUM value

i am always getting the SUM value not the individual values

[harshnathani]

@Anonymous ,

Make sure that the column is Don't Summarize.

Regards,

Harsh Nathani

[Anonymous]

Hi @Anonymous ,

Please check the formula as below, it should help get the value you want then put it to line chart.

Column = CALCULATE(SUM('Table'[Values]),FILTER('Table',YEAR('Table'[Date])=YEAR(EARLIER('Table'[Date]))-1&&'Table'[Program_ID]=EARLIER('Table'[Program_ID])))

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi thanks for the responds

but i am getting an error while implementing the exact formula

can you help me with this

i am not getting the exact values but the sum value

[Anonymous · Accepted solution]

Hi @Anonymous ,

Check the formula and the result as below.

Column = CALCULATE(SUM('Table'[value]),FILTER(ALLEXCEPT('Table','Table'[PM_ID]),MONTH('Table'[Month])=MONTH(EARLIER('Table'[Month]))&&YEAR('Table'[Month])=YEAR(EARLIER('Table'[Month]))-1))

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @Anonymous ,

I see you have duplicate PROGRAM_METRIC_ID in your table in same year. So you want the value group by month and PROGRAM_METRIC_ID instead of by year and PROGRAM_METRIC_ID? What's the expect out put in your visual?

Best Regards,

Jay

[Greg_Deckler]

Your logic for the previous date is not obvious to me but you will need to use the technique found here: See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...