Rolling 12 Months - end with the value for the last date.

[P-Lag]

Hi, 

I have used a dax measure to calculate a Rolling 12 Months Sales. The graph ends with a value drop when the specified date period has ended. I want it to end with the value for the last date. How can I amend the dax measure so the result will look like my below example?

Test 2, Rolling  12 (Revenue) =

CALCULATE (

      [Revenue €],

      DATESINPERIOD ( 'Dates'[Date],          

                      MAX ( 'Dates'[Date] ),  

                      -12,                   

                      MONTH                

      )

)

Best regards, Pauline

[P-Lag]

Hi,
Thanks for your reply!
I guess the 'Fact Table'[Date] - is the same as mine 'dates'[date]. Unfortunately, it didn't work - the result is the same.

Best regards, Pauline

Test X, Rolling  12 (Revenue) =

VAR MaxDate =

    MAX ( 'Dates'[Date] )

VAR MaxFactDate =

    CALCULATE ( MAX ( 'Dates'[Date] ), REMOVEFILTERS () )

RETURN

    IF (

        EOMONTH ( MaxDate, 0 ) <= EOMONTH ( MaxFactDate, 0 ),

        CALCULATE ( [Revenue €], DATESINPERIOD ( 'Dates'[Date], MaxDate, -12, MONTH ) )

    )

[johnt75]

No, the 'Fact Table'[Date] is the column in your fact table that the calendar table links to. It is supposed to find the last date when there was a transaction, and only return values on or before that date.

[P-Lag]

Hi,
Okay - then I think I understand. But It doesn't work - the result is still the same.

Test X, Rolling  12 (Revenue) =

VAR MaxDate =

    MAX ( 'Dates'[Date] )

VAR MaxFactDate =

    CALCULATE ( MAX ( Deliveries[Delivery Date]), REMOVEFILTERS () )

RETURN

    IF (

        EOMONTH ( MaxDate, 0 ) <= EOMONTH ( MaxFactDate, 0 ),

        CALCULATE ( [Revenue €], DATESINPERIOD ( 'Dates'[Date], MaxDate, -12, MONTH ) )

    )

[johnt75]

Do you have "show items with no data" checked? Also, what do you get if you turn the chart into a table instead?

[johnt75]

Try

Test 2, Rolling  12 (Revenue) =
VAR MaxDate =
    MAX ( 'Dates'[Date] )
VAR MaxFactDate =
    CALCULATE ( MAX ( 'Fact table'[Date] ), REMOVEFILTERS () )
RETURN
    IF (
        EOMONTH ( MaxDate, 0 ) <= EOMONTH ( MaxFactDate, 0 ),
        CALCULATE ( [Revenue €], DATESINPERIOD ( 'Dates'[Date], MaxDate, -12, MONTH ) )
    )

It checks to see if the max date from the visual is in the same month as the last date, or before, and will return a blank otherwise.