Sum Values Where Criteria Is Second Largest

[Anonymous]

Hi all,

I have a table, Pageviews, with three columns: Dates (everyday of the year going back to Jan 2018), Month Ref (six digit codes that reference Dates month and year - so, 201901 for Jan 2019, 201812 for Dec 2018, etc) and finally uPV - pageview data by day.

I am trying to generate a measure that dynamically sums all of the uPVs from two months ago. The logical way I can think of doing this is to find the second largest value in Month Ref and sum all corresponding values in uPV. However, there is no LARGE or such formula that I'm aware of so am a bit stuck.

Can anyone suggest how I might do this. See below a sample of the data I'm using. In this example, I would expect 11,473 returned as it would be summing everything corresponding to 201812 in Month Ref.

TIA,

SamB

Date	Month Ref	uPV
01/11/2018	201811	2441
02/11/2018	201811	2164
03/11/2018	201811	1910
04/11/2018	201811	2311
05/11/2018	201811	2060
06/11/2018	201811	1656
07/11/2018	201811	1657
01/12/2018	201812	1922
02/12/2018	201812	1342
03/12/2018	201812	2202
04/12/2018	201812	1611
05/12/2018	201812	1488
06/12/2018	201812	1540
07/12/2018	201812	1368
01/01/2019	201901	2226
02/01/2019	201901	2392
03/01/2019	201901	1938
04/01/2019	201901	1587
05/01/2019	201901	1098
06/01/2019	201901	1332
07/01/2019	201901	1418

[AlB · Accepted solution]

Hi @Anonymous

It would probably be easier to create a Date table and operate with it but if you don't want to do that, try this for your measure

Measure =
VAR _2MonthsPrior = EDATE ( TODAY (), -2 )
VAR _Month = MONTH ( _2MonthsPrior )
VAR _Year = YEAR ( _2MonthsPrior )
RETURN
    CALCULATE (
        SUM ( Table1[uPV] ),
        YEAR ( Table1[Date] ) = _Year,
        MONTH ( Table1[Date] ) = _Month
    )

[Anonymous]

Thank you @AlB - that works perfectly. Much appreciated!

SamB