Dynamic Rolling 12 Months including previous Month from Last Year

[Anonymous]

Hello,

I couldn't find anything in the forum or other website that related to what I'm trying to accomplished. 

Dec-2020	Jan-2021	Feb-2021	Mar-2021	Apr-2021	May-2021	Jun-2021	Jul-2021	Aug-2021	Sep-2021
Oct-2021	Nov-2021	Dec-2021

What I'm trying achieve is I want a rolling month including month of Dec-2020. Basically, if month of Jan-2021 has ended I want it dynamically to exclude month of Dec-2020. If today month is still Jan-2021 then include the previous month of Dec-2020.

I have created a formula but I don't if it will work and continue the trending, can some help me review my DAX?

RollingMonth = IF(Table[Date] <= TODAY(),
               IF(DATEDIFF(Table[Date], TODAY(),MONTH)+10 <= 11,
                TRUE(),FALSE()),FALSE())

Thank you for your help.

[v-shex-msft]

HI @Anonymous,

Can please share some dummy data with a similar data structure and expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[amitchandak]

@Anonymous , Not very clear with date table

example //last 12

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],eomonth(MAX('Date'[Date ]),-1) ,13,MONTH)) //+13 from last to next 12

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[Anonymous]

There @amitchandak

Thank you for your response, upon reviewing your formula. I don't want to sum anything or count I want a dynamic field as filter/slicer where I can select true or false.

[amitchandak]

@Anonymous , A new column like

RollingMonth = IF(Table[Date] > eomonth(TODAY(),-2) && Table[Date] <= eomonth(TODAY(),12) ,
TRUE(),FALSE())

or
RollingMonth = IF(Table[Date] > eomonth(TODAY(),-2) && Table[Date] <= eomonth(TODAY(),11) ,
TRUE(),FALSE())

[Anonymous]

I tried the last formula. It seems to be working but I don't want Nov-2020. 

RollingMonth = IF(Table[Date] > EOMONTH(TODAY(),-2) && Table[Date] <= EOMONTH(TODAY(),11) ,TRUE(),FALSE())

 

[v-shex-msft]

HI @Anonymous,

You can try to use the following formula, I added conditions to compare with the end date of the current month and use this in EOMONTH function:

RollingMonth =
VAR cDate =
    TODAY ()
RETURN
    IF (
        Table[Date]
            > EOMONTH ( cDate, IF ( DAY ( cDate ) = DAY ( EOMONTH ( cDate, 0 ) ), -2, -1 ) )
            && Table[Date] <= EOMONTH ( cDate, 11 ),
        TRUE (),
        FALSE ()
    )

Regards,

Xiaoxin Sheng

[Anonymous]

Hi @v-shex-msft 

It doesn't work, is only showing Jan-2021. Basically I want to include the previous Month of Dec-2020 if month of Jan-2021 hasn't pass yet. Let say If today is Feb-2021 then Dec-2020 should not be included in the rolling month.

I hope that make sense. Thank you

[v-shex-msft]

Hi @Anonymous,

So you mean the conversion only exists when the current date is in the ranges of the first two months?

If this is the case, you can try to use this formula, it contains the condition to check if the current date is February to ignore December data:

RollingMonth =
VAR cDate =
    TODAY ()
VAR prevMonth =
    EOMONTH ( cDate, IF ( MONTH ( cDate ) = 2, -3, -1 ) )
RETURN
    IF (
        Table[Date] > EOMONTH ( prevMonth, -11 )
            && Table[Date] <= prevMonth,
        TRUE (),
        FALSE ()
    )

Regards,

Xiaoxin Sheng