Customer Max Month sales volume on current year and Customer Max Month sales volume on prior year

[lsullivan6311]

Here is an example:

2021

Month       SalesVol

01 (Jan)     100

02 (Feb)     10

03(Mar)        5

04(Apr)         1

05(May)         2

06(Jun)          7

07(Jul)           25

08(Aug)         153

09(Sep)          29

10(Oct)          45

11(Nov)         125

12(Dec)          150

The max for this year is 153.

I need a max measure for current year

and one for prior year.

[v-rzhou-msft]

Hi @lsullivan6311 ,

I agree with Ashish_Mathur, I think your table should contain Years. My sample table is as below.

Measure:

MaxSale Current Year = CALCULATE(MAX('Table'[SalesVol]),FILTER(ALL('Table'),'Table'[Year] = YEAR(NOW())))

MaxSale Previous Year = CALCULATE(MAX('Table'[SalesVol]),FILTER(ALL('Table'),'Table'[Year] = YEAR(NOW())-1))

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lsullivan6311]

Yes, I did have year in my table, and this formula gave me the max for the current year and the previous year.  Is there a way if I have a group I would like to have the max by year that I could aggregate that way?  For example, for sales regions, so that I have a max year sales by each sales region?  Right now, it just puts in the max for every sales region.  The sales region is included in my table.

[v-rzhou-msft]

Hi @lsullivan6311 ,

I think you can try this code to calculate the max sales for each year and each Sales Region.

MaxSale each Year each Sales Region =
CALCULATE (
    MAX ( 'Table'[SalesVol] ),
    ALLEXCEPT ( 'Table', 'Table'[Year], 'Table'[Sales Region] )
)

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lsullivan6311]

This worked and I am able to get the max sales by region by year; however, now I am asked to provide a column in the Matrix for a difference in max from one year to another, say from 2020 to 2021, and another column with the % of 2021 to 2020.  Is this possible?  So, my Matrix would look like:

Sales Region       2020 Max Sales   2021 Max Sales    Increase/Decrease        % 2021/2020

        A                      100                        90                           -10                             90%

        B                       250                      275                            25                           110%  

        C                         5                          25                            20                           500%

[Ashish_Mathur · Accepted solution]

Share data in a proper format (which can be pasted to an MS Excel file) or share the download link of the PBI file.  Also, is there no year column?  Is there no Date column?  How does one differenciate between 2 years of data?