Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Goodmorning,
I am fairly new to Powerbi but loving it for the moment.
I have the following problem :
My data :
Year PubYear Category Value.
2009 2015 x 100
2009 2019 x 105
2009 2019 y 200
2009 2021 x 110
2010 2015 x 120
2010 2021 x 125
2010 2021 y 200
and so on for over 1.4 million records.
The Client wants 2 visuals :
First year by year growth for the last pubyear per year
Second Year by Year growth voor 1 year over different pubyears
How can achieve this using a Calendartable or dont i have to use this table ?
Solved! Go to Solution.
Hi @BartDekeyser ,
I created a sample pbix file(see the attachment), please check if that is what you want.
Year by year% =
VAR _selcat =
SELECTEDVALUE ( 'Table'[Category] )
VAR _selyear =
SELECTEDVALUE ( 'Table'[Year] )
VAR _selpubyear =
SELECTEDVALUE ( 'Table'[PubYear] )
VAR _prepubyear =
CALCULATE (
MAX ( 'Table'[PubYear] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Category] = _selcat
&& 'Table'[Year] = _selyear
&& 'Table'[PubYear] < _selpubyear
)
)
VAR _prepyvalue =
CALCULATE (
SUM ( 'Table'[Value] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Category] = _selcat
&& 'Table'[Year] = _selyear
&& 'Table'[PubYear] = _prepubyear
)
)
RETURN
IF (
ISBLANK ( _prepyvalue ),
BLANK (),
DIVIDE ( SUM ( 'Table'[Value] ) - _prepyvalue, _prepyvalue )
)
In addition, you can refer the following videos to get it.
Power BI Year-Over-Year Growth % Calculations [DAX] - YouTube
Power BI YEAR over YEAR (YoY) Change Calculations [DAX]
If the above one can't help you, could you please explain your expected result base on your shared sample data?Thank you.
Best Regards
Hi @BartDekeyser ,
I created a sample pbix file(see the attachment), please check if that is what you want.
Year by year% =
VAR _selcat =
SELECTEDVALUE ( 'Table'[Category] )
VAR _selyear =
SELECTEDVALUE ( 'Table'[Year] )
VAR _selpubyear =
SELECTEDVALUE ( 'Table'[PubYear] )
VAR _prepubyear =
CALCULATE (
MAX ( 'Table'[PubYear] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Category] = _selcat
&& 'Table'[Year] = _selyear
&& 'Table'[PubYear] < _selpubyear
)
)
VAR _prepyvalue =
CALCULATE (
SUM ( 'Table'[Value] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Category] = _selcat
&& 'Table'[Year] = _selyear
&& 'Table'[PubYear] = _prepubyear
)
)
RETURN
IF (
ISBLANK ( _prepyvalue ),
BLANK (),
DIVIDE ( SUM ( 'Table'[Value] ) - _prepyvalue, _prepyvalue )
)
In addition, you can refer the following videos to get it.
Power BI Year-Over-Year Growth % Calculations [DAX] - YouTube
Power BI YEAR over YEAR (YoY) Change Calculations [DAX]
If the above one can't help you, could you please explain your expected result base on your shared sample data?Thank you.
Best Regards
i tried you solution, but it is not what i wanted. I want to see the evolution through the years using their maximum publication year per categorie. So
For X 2009's max pubyear = 2021 for Y 2009's max pubyear = 2019, for 2010 for x and y it is 2021.
And on another visual i would want to see the evolution of 2009 through different pubyears.
So for X on 2009 i want to see 2015 vs 2019 vs 2021
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
111 | |
107 | |
106 | |
90 | |
61 |
User | Count |
---|---|
165 | |
136 | |
134 | |
97 | |
86 |