Fill down all matched value same year

[hoangDAO81]

Hi, i am new to Power Bi, i have some problems when i try to compare value to base year, the table kind of like this 

Total E

ID	Para	Year	Data	Baseyear	Energy consumption BY
A	Energy comsumption	2018	200	2018
A	Energy comsumption	2019	300	2018
A	Energy comsumption	2020	400	2018
B	Energy comsumption	2019		2019
B	Energy comsumption	2020		2019
C	Energy comsumption	2019		2019

I tried sameperiodlastyear but it not apply to difference base year, also i need a measure to check base year and return value as base year value. 
Energy consumption BY = CALCULATE([Energy comsumption],'Total E'[Baseyear] = 'Total E'[Year])

Then it return only matched value

Year	Energy comsumption	Enrgy consumption BY
2018	200	200
2019	300
2020	400

Thanks in advance!

[v-cgao-msft · Accepted solution]

Hi @hoangDAO81 ,

Is it necessary to compare [ENERGY CONSUMPTION BY] under the same ID? If so, try this measure:

Energy consumption BY = 
VAR _base_year = SELECTEDVALUE('Total E'[Baseyear])
VAR _result = CALCULATE([Energy comsumption], 'Total E'[Year]=_base_year)
RETURN
_result

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

[hoangDAO81]

Dear Gao,

This is close with what i need but the values are not show as your table, they only show year 2018, can you refine some of your DAX. Thank you!

[v-cgao-msft]

Hi @hoangDAO81 ,

Both [ID] and [Year] fields need to be placed in the table visual.

Grouping by year only will result in multiple Base Years corresponding to Years. Or is there some other unspecified calculation logic?

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

[amitchandak]

@hoangDAO81 , First all all have separate Year/Date table and join back with date or year and then have measures like

//Only year vs Year, not a level below

This Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

Fixed Base year

Base  Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=2018 ))

or

This Year =

Var _min = minx(allselected(Date), Date[Year])

return

CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=_min ))

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA