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

 

 

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.

 

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

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


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


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

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?

  • 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

      • 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