SUM Values Based On Second Biggest Value From Another Column

[Anonymous]

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!

[Mariusz · Accepted solution]

Hi @Anonymous 

You can use PARALLELPERIOD function

https://docs.microsoft.com/en-us/dax/parallelperiod-function-dax

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Anonymous]

you are genius @Mariusz 🙂 

[Anonymous]

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

[Anonymous]

Ok, below will solve the issue. Not sure if that's the easiest way, but it works.

Unbilled-Previous-Month-02 =

VAR Period01 = [Measure-00]

RETURN

if([Measure-00]<>0,calculate(sum('All BPOC Reports'[Unbilled]),'All BPOC Reports'[SourceDate]=Period01),[Unbilled-Previous-Month])

Measure-00 =

VAR Date1 = [Max-Period-01]

VAR Date2 = CALCULATE(MAX('All BPOC Reports'[SourceDate]),FILTER('All BPOC Reports','All BPOC Reports'[SourceDate]<Date1))

RETURN Date2