Forum Discussion

Pbiuserr's avatar
Pbiuserr
Post Prodigy
4 years ago

Show previous 6 months from sqlbi does not work

Hi,

trying to follow this guide on my dataset (can't share data unfortunately) show last 6 months based on user slicer selection 

 

My data model is similar - many to one relationships between fact table & date & prev date

Perhaps its my calculation? I skipped tabular editor and my measure looks like

 

Prev 6 month =
 
VAR NumOfMonths = -6
VAR ReferenceDate = MAX ( Date[Date] )
VAR PreviousDates =
DATESINPERIOD (
NewDate[Date],
ReferenceDate,
NumOfMonths,
MONTH
)
VAR Result =
CALCULATE (
[Total Created],         --- this one is COUNT(Data[CREATED])
REMOVEFILTERS ( Date[Date] ),
KEEPFILTERS ( PreviousDates ),
USERELATIONSHIP ( NewDate[Date], Date[Date] )
)
RETURN Result
 
My slicer has year-month from Date table, put in the table value of month-year from NewDate table and this "Prev 6 month" measure. It filters only selected month-year, not -6
 
any ideas why it doesn't work?
Need Help

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Pbiuserr , You need to have independent table if you select one month and want more than that

     

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

     

    also check

    Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI

  • AllisonKennedy's avatar
    AllisonKennedy
    Community Champion
    4 years ago

    Pbiuserr  You need to remove filters from the entire Date table: 

     

    Prev 6 month =
     
    VAR NumOfMonths = -6
    VAR ReferenceDate = MAX ( Date[Date] )
    VAR PreviousDates =
    DATESINPERIOD (
    NewDate[Date],
    ReferenceDate,
    NumOfMonths,
    MONTH
    )
    VAR Result =
    CALCULATE (
    [Total Created],         --- this one is COUNT(Data[CREATED])
    REMOVEFILTERS ( 'Date' ),
    KEEPFILTERS ( PreviousDates ),
    USERELATIONSHIP ( NewDate[Date], Date[Date] )
    )
    RETURN Result
    • Pbiuserr's avatar
      Pbiuserr
      Post Prodigy
      4 years ago

      AllisonKennedy 
      Hello
      Seems reasonable however now nothing happens, on any combination of slicers I got no value from Prev 6 month measure

      • AllisonKennedy's avatar
        AllisonKennedy
        Community Champion
        4 years ago

        Pbiuserr  no value, or just one month? Are you able to share any screenshots with sensitive data blacked out?