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

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

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

Martin_D's avatar
Martin_D
Icon for Solution Sage rankSolution Sage
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

    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.

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

    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