Top 2 periods

[vipett]

I'm converting a report into measures from using several large cross joined calculated tables with calculated columns as it was way too slow to work in

I have a couple of Periods (months from a date table) and depending on what version you pick, it shows only the applicable periods.

i.e if I choose July 2023 as the version, it shows July 2023-June 2024 + May & june 2023 (for historical values).

In one comparison calculation I want to exclude the last two months, i.e may and june 2024.

to filter out the last period is easy with 

Measure = IF(SELECTEDVALUE('Calendar Demand'[DemandDate YYMM])=CALCULATE(MAX('Calendar Demand'[DemandDate YYMM]),ALL('Calendar Demand'[DemandDate YYMM])),1,0)

But how do I get the two last periods, I've tried with TopN but can't really get it to work..

Edit: to clarify, it is not the top 2 based on any values or calculations, just the two last months..

[amitchandak]

@vipett , Not very clear, Assume you want to select 2 month using a selection of one month you need independent date table for Slicer

//Date1 is an independent Date table, Date is joined with Table
Last 2 month =
var _max = maxx(allselected(Date1),Date1[Date])
var _min = eomonth(_max, -2) +1
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))

two months before it

//Date1 is an independent Date table, Date is joined with Table
new measure =
var _max = eomonth(maxx(allselected(Date1),Date1[Date]),-2)
var _min = eomonth(_max, -2) +1
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))

You can also use rolling or window, in case you do need trend 

Rolling 2 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-2,MONTH))

Rolling 2 before 2 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],eomonth(MAX('Date'[Date]),-2) ,-2,MONTH))

Rolling 2 = CALCULATE([Net], WINDOW(-1,REL, 0, REL, ADDCOLUMNS(ALLSELECTED('Date'[Month Year],'Date'[Month Year Sort] ),ORDERBY([Month Year Sort],asc)))

Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc

[vipett · Accepted solution]

Thanks, yes it was perhaps a bit unclear, I managed to solve it this way:

Two last periods =

 var latest = CALCULATE(MAX('Calendar Demand'[DemandDate YYMM]),ALL('Calendar Demand'[DemandDate YYMM]))

 var second =  CALCULATE(MAX('Calendar Demand'[DemandDate YYMM]),'Calendar Demand'[DemandDate YYMM]<>latest)

 return

 IF(SELECTEDVALUE('Calendar Demand'[DemandDate YYMM])=latest,1,IF(SELECTEDVALUE('Calendar Demand'[DemandDate YYMM])=Second,1,0))