Forum Discussion

vipett's avatar
vipett
Icon for Helper III rankHelper III
3 years ago
Solved

Top 2 periods

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..
Need Help
  • vipett's avatar
    vipett
    3 years ago

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

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

    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's avatar
      vipett
      Icon for Helper III rankHelper III
      3 years ago

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