Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

SUM Values Based On Second Biggest Value From Another Column

Hi everyone,

 

I have a table that contains many rows. One of the columns is period (YYYYMMDD). Another column is the Sales Amount.

 

What I want to do is to have a column (measure?) that will show me the sales amount for the second "biggest" (aka max) month. Note that many rows will have the same period.

 

Index | Sales Amount | Period
01----------------100-20200130
02----------------150-20200130
03----------------250-20200130

04----------------500-20200130

05----------------500-20200228

06----------------500-20200228

07---------------1000-20200228

 

So, I want to have a table like this:

 

   Period   |  Sales  | Sales vPM
20200228 - 2000 -  1000

 

I do have a measure RANK 

RANK =
RANKX (
ALLSELECTED ( 'All BPOC Reports'[Period_Number] ),
CALCULATE ( MAX('All BPOC Reports'[Period_Number] ) ),
,
DESC,
DENSE
)
 
But I don't know what to do next...

 

thank you for the help!

Need Help

4 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      you are genius Mariusz ğŸ™‚ 

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Actually I came across a similar challenge.

         

        The metric you suggested: [Unbilled-Previous-Month = CALCULATE(SUM('All BPOC Reports'[Unbilled]), PARALLELPERIOD('All BPOC Reports'[SourceDate],-1,MONTH)] is working great if I have a table and one of my columns is Dates.

         

        However, what I also would like to have is a stand-alone multi-row card that will always show me two things:

        - Current Month Unbilled [Highest Month, Max]

        - Previous Month Unbilled*

         

        *Previous Month Unbiled (formula above) is showing me the total of all Unbilled values from my table. However, if I select one of the specific Dates, then it will show correctly.

         

        My desired output is:

        a) if nothing is selected, show me the Current Month Unbilled (Highest | Max Month) & Previous Month Unbilled

        b) if I select 1/31/2020, show me the Current Month Unbilled (75M) & Previous Month Unbilled (60M)

         

         

        Point b) is working well, I just need to fix the point a) somehow. I was thinking about embedding MAX formula or creating a var with M-1, but I got a bit lost...