Rolling 12 months using year and month column

[Anonymous]

Hello Community,

            I am trying to create a measure for rolling 12 months.

I don't have day column. So I cannot use DATE functions. Please help me creating the measure.

Desired Output: If I select 08/2018, then it should show previous 12 months rolling total.

Thanks

[jaswu23]

Hi - I implmented the last year 12 month rolling (usage SAMEPERIODLASTYEAR).

When graphing the data it's displaying Jan to Dec instead of from Jun to Jul (ie the chosen month Jul-19). How can I make it display dynamically based on the chosen end month period showing last.

[Anonymous]

Hi,

I am trying to display last 13 months data by clicking on year and month on the slicer. Is there a way we can display the last 12 months data because the table is having a relationship with the dates table.

For example when i click on year 2020 and month Sept it should display up to Sept 2019.

Can anyone kindly assist me what DAX should i use?

Thanks in advance.

Best Regards,

R

[v-cherch-msft · Accepted solution]

Hi @Anonymous

Attached the sample file for your reference.

Measure =
IF (
    HASONEVALUE ( 'Calendar'[YearMonth] ),
    CALCULATE (
        SUM ( Data[Sales] ),
        FILTER (
            ALL ( 'Data'[Order] ),
            'Data'[Order]
                >= SELECTEDVALUE ( 'Calendar'[Order_Calendar] ) - 11
                && Data[Order] <= SELECTEDVALUE ( 'Calendar'[Order_Calendar] )
        )
    ),
    CALCULATE ( SUM ( Data[Sales] ) )
)

Regards,

Cherie

[LivioLanzo]

Hello @Anonymous

As an alternative if you do not want to use a normal Date dimension, you can build a model such as the one below and then use this simple measure:

= 
CALCULATE(
    SUM( Sales[Sales] ),
    FILTER(
        ALL( Dates[Month Sequence] ),
        AND(
            Dates[Month Sequence] >= MAX( Dates[Month Sequence] ) - 11,
            Dates[Month Sequence] <= MAX( Dates[Month Sequence] )
        )
    ),
    ALL( Dates )
)

[dedelman_clng]

Assume each value is as of the first of the month, and then you can create a date out of your month and year

Dt = DATE([Year], [Month], 1)

Attach to a calendar table and you can do time intelligence

R12 Measure = CALCULATE([Measure], DATESINPERIOD(DateTab[Date], MAX(DateTab[Date]), -1, YEAR) )

Hope this helps

David