Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

HI BunnyV ,

It seems like you are calculate rolling across multiple date fields, can you please share some dummy data for test? 

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

  • BunnyV's avatar
    BunnyV
    Frequent Visitor
    6 years ago

    Anonymous Greg_Deckler amitchandak Thanks for all of your replies.

     

    Please find the below sample data which i am calculating TTM for.

    MonthVALUE
    Jan-181194
    Feb-181103
    Mar-181313
    Apr-181289
    May-181363
    Jun-181317
    Jul-181518
    Aug-181560
    Sep-181474
    Oct-181420
    Nov-181487
    Dec-181526
    Jan-191437
    Feb-191459
    Mar-191650
    Apr-191470
    May-191699
    Jun-191421
    Jul-191591
    Aug-191549
    Sep-191517
    Oct-191275
    Nov-191417
    Dec-191428
    Jan-20197

     

    I have this TTM data table and then, i have create a Calender table which dont have any relation with data table.

    But i am using the YEAR column to filter out the years from Calender as a relative filter using another measure.

    _TTM Measure is as i mentioned in my question, and the relative filter measure which filters last 12 months is

     

    _TTM =
    VAR CurrentDate = MAX(TTM[Date])
    VAR EndDate = MAX(Calender[EOMONTH])
    VAR PreviousDate = DATE(YEAR(CurrentDate),MONTH(CurrentDate)-11,DAY(CurrentDate))
    VAR Result =
    CALCULATE(SUM(TTM[VALUE]),
    FILTER(ALLSELECTED(TTM),
    TTM[Date] >= PreviousDate && TTM[Date] <= CurrentDate
    ))
    VAR MonthNo =
    CALCULATE(DISTINCTCOUNT(TTM[Month]),
    FILTER(ALLSELECTED(TTM),
    TTM[Date] >= DATE(YEAR(CurrentDate),MONTH(CurrentDate)-11,DAY(CurrentDate)) && TTM[Date] <= CurrentDate
    ))
    RETURN
    Result/MonthNo
    ----
    _Last12Months =
    VAR TTMDATE = MAX(TTM[Date])
    VAR CurrentDate = MAX(Calender[Date])
    VAR PreviousDate = DATE(YEAR(CurrentDate),MONTH(CurrentDate)-11,DAY(CurrentDate))
    VAR SelectedYear = SELECTEDVALUE(TTM[Year])
    RETURN
    SWITCH(TRUE(),
    TTMDATE <= CurrentDate && TTMDATE >= PreviousDate,"12Months","Others")

    Output:-
     
    Now the values which are coming are absolute fine, and if you see even though i selected the value from YEAR slicer there is no filter applied onto the table because there is no relation from TTM data table to caleder.
    To filter out last 12 months data accordingly, 
    I put that _Last12Months measure into visual level filter and filteredout the months.
    But then the values are getting changed and the values are coming wrong.
     
    The values which i am getting before filtering 12months are the right values.
     
    Can anyone please guide me where is the issue is.
     
    Thanks,
    Bunny.

     

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi BunnyV ,

        Maybe you can try to add an if statement to filter current and previous year based don selected calendar date:

        _TTM =
VAR CurrentDate =
    MAX ( TTM[Date] )
VAR EndDate =
    MAX ( Calender[EOMONTH] )
VAR PreviousDate =
    DATE ( YEAR ( CurrentDate ), MONTH ( CurrentDate ) - 11, DAY ( CurrentDate ) )
VAR Result =
    CALCULATE (
        SUM ( TTM[VALUE] ),
        FILTER (
            ALLSELECTED ( TTM ),
            TTM[Date] >= PreviousDate
                && TTM[Date] <= CurrentDate
        )
    )
VAR MonthNo =
    CALCULATE (
        DISTINCTCOUNT ( TTM[Month] ),
        FILTER (
            ALLSELECTED ( TTM ),
            TTM[Date]
                >= DATE ( YEAR ( CurrentDate ), MONTH ( CurrentDate ) - 11, DAY ( CurrentDate ) )
                && TTM[Date] <= CurrentDate
        )
    )
VAR cYear =
    YEAR ( MAX ( calndar[Date] ) )
RETURN
    IF ( YEAR ( CurrentDate ) IN { cYear - 1, cYear }, Result / MonthNo )

        Notice: add a visual filter to hide records who not contain correspond measure results.

        Regards,

        Xiaoxin Sheng