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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
AnthonyJoseph
Resolver III
Resolver III

Show data based on the latest/maximum year

Dear community,

 

I have bar graph were we have plotted total revenvue against clients (hierarchy of client and projects). Now, the new requirement is to calculate revenue only for the Projects that we in the latest year and their corresponding values for all other selected years. Please can someone guide me how to achieve it.

For example: In the below sample data below are the expected outcomes

1) when 2020 and 2021 years are selected.  Client 1 should show 300 (Summing up project 1 for years 2022 and 2021) and client 2 should show 150 (showing project 1 for year 2022. As there no record for project 1 in 2021 ).

 

2) 2022, 2021 and 2020 ( when these years are selected). Projects in 2022 are filtered i.e. Project 1 in client 1 and Project A in client 2. show revenue on client level i.e. Client 1 should show 100 and client 2 should show 150.

 

 

ClientProjectYearrevenue
Client 1Project 12022100
Client 1Project 12021200
Client 1Project 12020500
Client 1Project 22021100
Client 1Project 2202040
Client 2Project A2022150
Client 2Project 22021250

 

Appreciate if someone can suggest ideas to achieve the above results.

 

Thanks,

AnthonyJoseph

 

1 ACCEPTED SOLUTION

Hi @AnthonyJoseph ,

This code should match better with your requirements:

Revenue = 
VAR _LastYear = 
    CALCULATE (
        MAX ( 'Table'[Year] ),
        ALLSELECTED ()
    )
VAR _ProjectsLastYear = 
    CALCULATETABLE (
        VALUES ( 'Table'[Project] ),
        'Table'[Year] = _LastYear,
        ALLSELECTED ()
    )
VAR _ClientsLastYear = 
    CALCULATETABLE (
        VALUES ( 'Table'[Client] ),
        'Table'[Year] = _LastYear,
        ALLSELECTED ()
    )
VAR _ProjectsInContext = VALUES ( 'Table'[Project] )
VAR _ClientsInContext = VALUES ( 'Table'[Client] )
VAR _ClientProjectCombinationsInLastYear =
    CALCULATETABLE (
        SUMMARIZE (
            'Table',
            'Table'[Client],
            'Table'[Project]
        ),
        'Table'[Year] = _LastYear,
        ALLEXCEPT ( 'Table', 'Table'[Client], 'Table'[Project] )
    )
VAR _RevenuePerClientProject =
    CALCULATETABLE (
        ADDCOLUMNS (
            _ClientProjectCombinationsInLastYear,
            "@Revenue",
            CALCULATE ( SUM ( 'Table'[revenue] ) )
        ),
        INTERSECT ( _ClientsLastYear, _ClientsInContext ),
        INTERSECT ( _ProjectsLastYear, _ProjectsInContext )
    )
RETURN
SUMX ( _RevenuePerClientProject, [@Revenue] )

 

revenue projects last year examplesrevenue projects last year examples

The 40 for Client 2, Project 2, Year 2020 in your examples seems rather like a mistake in your example?Example not matchingExample not matching
BR
Martin

View solution in original post

9 REPLIES 9
Martin_D
Super User
Super User

It took me a moment to get the general rules from your examples, but here you are:

 

 

Revenue of Last Year's Projects = 

VAR _LastYearInOverallData = // this is what you call latest year
    CALCULATE (
        MAX ('Table'[Year] ),
        ALL ()
    )

VAR _SelectAllProjectsInLastYearInOverallData =
    CALCULATETABLE (
        SUMMARIZECOLUMNS(
            'Table'[Client],
            'Table'[Project] 
        ),
        'Table'[Year] = _LastYearInOverallData
    )

VAR _LastYearInFilterContext = 
    CALCULATE (
        MAX ( 'Table'[Year] ),
        ALLEXCEPT ( 'Table', 'Table'[Year] )
    )

VAR _TotalSinceLatestYearInFilterContextForAllProjectsInLastYearInOverallData = 
    CALCULATE (
        SUM ( 'Table'[revenue] ),
        _SelectAllProjectsInLastYearInOverallData,
        'Table'[Year] >= _LastYearInFilterContext
    )

RETURN

_TotalSinceLatestYearInFilterContextForAllProjectsInLastYearInOverallData

 

 

Last year's projects and values since last selected yearLast year's projects and values since last selected year

 

 

github.pnglinkedin.png

Thanks @Martin_D . The solution always compares against the latest in the dataset whereas it should compare against the latest/maximum year among the selected year from the slicer.
In the screenshot below, the years 2021 and 2020 are selected but the graph shows data for 2022 i.e. didnt consider the latest value of the slicer but considers for that data set. Paster below are the screenshots for reference.

Please can you help me to show data only based on the latest selected value from the slicer,,,

AnthonyJoseph_1-1688368479231.png

 




AnthonyJoseph_0-1688368067472.png

 

Hi @AnthonyJoseph ,

To be honest, I don't really get the requirements. My screenshot shows exactly the selection that you described in your examples and exactly the resulting values that you required for these selections. Would you please doublecheck your examples or add more examples that make the difference clear between my proposed solution and your requirement. Ideal would be a general description in addition to examples and consitent with the examples. Actually I wrote alternative measures that matched more with my understanding of your textual description but they did not match with your examples. I'm a bit confused.
BR
Martin

Hi @Martin_D 
Sorry for the inconvenience... My bad.. I gave the incorrect examples....

I am trying the better examples below with the screenshots...
1) When 2020, 2021, 2022 are selected then the clients and projects in the latest year is selected (2022 in this case) and their corresponding values for the selected years (2020 and 2021) are also shown.

AnthonyJoseph_6-1688373343281.png

 

 

2)Scenario 2: When the years 2021 and 2020 are selected, the clients and projects in the latest year is selected (2021 in this case) and the values for the selected years (2021 and 2020) are also displayed.

AnthonyJoseph_3-1688373107117.png

 

3) Scenario 3: When 2022 and 2020 are selected. 

AnthonyJoseph_7-1688373373825.png

4) When single year (2021 in this case) is selected only the revenue for the selected year should be displayed.

AnthonyJoseph_8-1688373484395.png


Hope I was able to give a better understanding of the problem this time...(Again, sorry for the incorrect examples)...Realy appreciate your help in this. Please let me know if any further clarification is required...

 

Thanks,
AnthonyJoseph


When trying to describe the specific examples in more general rules, would you refer to 2022 as the last complete calender year in general, or the last year with data in general, i.e. next year, in 2024, the rules that apply now for 2022 would then apply to year 2023 or do you want a solution in which the behavior is controlled specifically for the given absolute years, meaning, switching behavior based on whether 2022 is included in the selection, hard coded?

Hi @Martin_D 
Yes, its the last year with data in general...

Hi @AnthonyJoseph ,

This code should match better with your requirements:

Revenue = 
VAR _LastYear = 
    CALCULATE (
        MAX ( 'Table'[Year] ),
        ALLSELECTED ()
    )
VAR _ProjectsLastYear = 
    CALCULATETABLE (
        VALUES ( 'Table'[Project] ),
        'Table'[Year] = _LastYear,
        ALLSELECTED ()
    )
VAR _ClientsLastYear = 
    CALCULATETABLE (
        VALUES ( 'Table'[Client] ),
        'Table'[Year] = _LastYear,
        ALLSELECTED ()
    )
VAR _ProjectsInContext = VALUES ( 'Table'[Project] )
VAR _ClientsInContext = VALUES ( 'Table'[Client] )
VAR _ClientProjectCombinationsInLastYear =
    CALCULATETABLE (
        SUMMARIZE (
            'Table',
            'Table'[Client],
            'Table'[Project]
        ),
        'Table'[Year] = _LastYear,
        ALLEXCEPT ( 'Table', 'Table'[Client], 'Table'[Project] )
    )
VAR _RevenuePerClientProject =
    CALCULATETABLE (
        ADDCOLUMNS (
            _ClientProjectCombinationsInLastYear,
            "@Revenue",
            CALCULATE ( SUM ( 'Table'[revenue] ) )
        ),
        INTERSECT ( _ClientsLastYear, _ClientsInContext ),
        INTERSECT ( _ProjectsLastYear, _ProjectsInContext )
    )
RETURN
SUMX ( _RevenuePerClientProject, [@Revenue] )

 

revenue projects last year examplesrevenue projects last year examples

The 40 for Client 2, Project 2, Year 2020 in your examples seems rather like a mistake in your example?Example not matchingExample not matching
BR
Martin

Perfect thank you so much @Martin_D ... This solution is working as expected.... Just one more question, Since this solution has calculation that is straight forward (i.e. sum of revenue). I have few other measures that does calculations from other related tables in the data model as well.... Could you please advise how to leverage this calculation to those measures...
For example: To find the variance between revenue and forecasted revenue I use a calculation like:

% variance = Divide( sum(Table[revenue]) -  sum(Table[forecasted revenue]),sum(Table[forecasted revenue]))

Please can you advice how we can use this solution for this %variance situation....

Thank you so much for your inputs again....

 

Thanks,

AnthonyJoseph 

Actually you can do this in the same code structure. The first variables are all just to set up the valid categories (clients, project), you can reuse them. Then you need two variables following the _RevenuePerClientProject pattern, one for revenue and one for forecast. Then you need two variables that take the result of the SUMX of each, one for revenue and one for forecast. Then you do the DIVIDE following your existing pattern.

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 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.

Sept NL Carousel

Fabric Community Update - September 2024

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