create a table which contains value for last 13 months from the given date

[priyanath1988]

Hi, I am seeking help in created a calculated table whoch contains value for last 13 months.

this is the formula I am using but it is not filtering the rows

BS_Details_Last13Months = CALCULATETABLE(GL_DETAIL, FILTER(GL_DETAIL,GL_DETAIL[ENTRY_DATE] >= DATEADD(GL_DETAIL[previous_month], -13, month)))

Note: 

previous_month = EOMONTH(DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY())),0)  --> basically I have to calculate last 13 months from the previous month ofthe current date. for example if i run this query today which is march. then in the previous month the value should be last date of Feb.

[andhiii079845]

Where is now your problem 🙂

[priyanath1988]

@andhiii079845 it is not filtering. it is still showing older rows.

[v-rzhou-msft · Accepted solution]

Hi @priyanath1988 ,

If you want to calculate the value for last 13 months from the given date, I suggest you to try below code.

BS_Details_Last13Months =
VAR _LAST13MONTH =
    EOMONTH ( MAX ( GL_DETAIL[previous_month] ), -13 )
RETURN
    CALCULATETABLE (
        GL_DETAIL,
        FILTER (
            GL_DETAIL,
            GL_DETAIL[ENTRY_DATE] >= _LAST13MONTH
                && GL_DETAIL[ENTRY_DATE] <= MAX ( GL_DETAIL[previous_month] )
        )
    )

My Sample:

Result is as below.

 

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.