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 ...
Need Help
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