Forum Discussion

Pbiuserr's avatar
Pbiuserr
Post Prodigy
4 years ago

Last 12 months by SQLBI problem

Hello,
I've created exactly what's here Show last 6 months but for last year (-13) and it works good, however if I have current month (so today is April) it goes from April 2022 till March 2021, so last 13 months, not 12. If I change to any other period it works good

 

Any idea how to overcome that? I can see that it gets "Ordinal -1" - i dont know what it is, perhaps its that what casues the problem?

 

Current month in slicer:

 

Any other month:

Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Pbiuserr , Can you share the formula you have used

     

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

     

    • Pbiuserr's avatar
      Pbiuserr
      Post Prodigy
      4 years ago

      amitchandak 
      hi
      alike in video, its calculated group. i know there are other approaches but Ive used the one presented in the clip and If I'd need to reverse it, then it would be too much time consuming so I rely on that

      VAR NumOfMonths = -13
      VAR ReferenceDate = MAX ( 'Calendar'[Date] )
      VAR PreviousDates =
      DATESINPERIOD (
      'Period prev'[Date],
      ReferenceDate,
      NumOfMonths,
      MONTH
      )
      VAR Result =
      CALCULATE (
      SELECTEDMEASURE(),
      REMOVEFILTERS ( 'Calendar' ),
      KEEPFILTERS ( PreviousDates ),
      USERELATIONSHIP ( 'Period prev'[Date], 'Calendar'[Date] )
      )
      RETURN Result