Forum Discussion

AnthonyJoseph's avatar
AnthonyJoseph
Icon for Resolver III rankResolver III
3 years ago
Solved

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

 

@Dax
  • Martin_D's avatar
    Martin_D
    3 years ago

    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 examples

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

9 Replies

  • Martin_D's avatar
    Martin_D
    Icon for Solution Sage rankSolution Sage
    3 years ago

    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 year

     

     

    • AnthonyJoseph's avatar
      AnthonyJoseph
      Icon for Resolver III rankResolver III
      3 years ago

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

       




       

      • Martin_D's avatar
        Martin_D
        Icon for Solution Sage rankSolution Sage
        3 years ago

        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