Forum Discussion
Top 2 periods
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)returnIF(SELECTEDVALUE('Calendar Demand'[DemandDate YYMM])=latest,1,IF(SELECTEDVALUE('Calendar Demand'[DemandDate YYMM])=Second,1,0))
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
Thanks, yes it was perhaps a bit unclear, I managed to solve it this way: