Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
BartDekeyser
Frequent Visitor

Calendar, multiple dates but dates depend on each other

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 ?

 

 

  

 

 

 

 

 

1 ACCEPTED SOLUTION
v-yiruan-msft
Community Support
Community Support

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 )
    )

 

vyiruanmsft_0-1684468153646.png

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]

vyiruanmsft_1-1684468310967.png

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

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

View solution in original post

2 REPLIES 2
v-yiruan-msft
Community Support
Community Support

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 )
    )

 

vyiruanmsft_0-1684468153646.png

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]

vyiruanmsft_1-1684468310967.png

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

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

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.