Forum Discussion

hoangDAO81's avatar
hoangDAO81
Regular Visitor
2 years ago
Solved

Fill down all matched value same year

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

IDParaYearDataBaseyearEnergy consumption BY
AEnergy comsumption20182002018 
AEnergy comsumption20193002018 
AEnergy comsumption20204002018 
BEnergy comsumption2019 2019 
BEnergy comsumption2020 2019 
CEnergy comsumption2019 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

YearEnergy comsumptionEnrgy consumption BY
2018200200
2019300 
2020400 

Thanks in advance!

 

Need Help
Tutorial Requests

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    2 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      hoangDAO81
      Regular Visitor
      2 years ago

      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!