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 tha...
@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

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