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