how to get last 12 months from today's date ?

[AnandRanga]

I want to create a chart in which I want to show a measure(profit %) that is already calculated, but the problem is I want to show it as line chart on the basis of last 12 months.

I have a column in database that contains all the date(Master table).  I am getting date of last year like this:

Last 12 months date= (DATE(YEAR([today's_Date]),MONTH([today's_Date]),day([today's_Date])))-365

that is giving me 2 feb 2016 12:00:00 AM

next I am doing

Last12_Now = DATESBETWEEN(TIMESHEETANALYSISCUBE[TRANSDATE],[Last 12 months],[today's_Date])

but it's not working.

so basically I want to show that measure (profit %) from feb 2016 to feb 2017.

How to do this in Power BI using DAX ?

Thanks for your time.

[v-ljerr-msft · Accepted solution]

Hi @AnandRanga,

You should be able to follow steps below to get your expected result.

1. Use the formula below to create a calculate column called "YearMonth" in your Date table.

YearMonth = FORMAT(Date[Date],"YYYYMMMM")

2. Use the formula below to create another calculate column called "DateInt" in your Date table.

DateInt = YEAR(Date[Date])*12+MONTH(Date[Date])

3. Use the formula below to create a new measure for profit % called "Last 12 Montn Profit %".

Last 12 Montn Profit % =
IF (
    MAX ( Date[DateInt] )
        <= YEAR ( TODAY () ) * 12
            + MONTH ( TODAY () )
        && MAX ( Date[DateInt] )
            > YEAR ( TODAY () ) * 12
                + MONTH ( TODAY () )
                - 12,
    [Profit %],
    BLANK ()
)

4. Last, just show the "Last 12 Montn Profit %" measure on the Chart with the "YearMonth" column of Date table.

Regards

[rkalantri]

---

@v-ljerr-msft wrote:

Hi @AnandRanga,

You should be able to follow steps below to get your expected result.

1. Use the formula below to create a calculate column called "YearMonth" in your Date table.

YearMonth = FORMAT(Date[Date],"YYYYMMMM")

2. Use the formula below to create another calculate column called "DateInt" in your Date table.

DateInt = YEAR(Date[Date])*12+MONTH(Date[Date])

3. Use the formula below to create a new measure for profit % called "Last 12 Montn Profit %".

Last 12 Montn Profit % =
IF (
    MAX ( Date[DateInt] )
        <= YEAR ( TODAY () ) * 12
            + MONTH ( TODAY () )
        && MAX ( Date[DateInt] )
            > YEAR ( TODAY () ) * 12
                + MONTH ( TODAY () )
                - 12,
    [Profit %],
    BLANK ()
)

4. Last, just show the "Last 12 Montn Profit %" measure on the Chart with the "YearMonth" column of Date table.

Regards

---

I tried to use this formula.. Alo creted a first 2 column and 3rd one also as column as i wanted to use it in slicer. here is my code below:

IsLast12Months = IF (
    MAX ( dCalender[DateInt] )
        <= YEAR ( TODAY () ) * 12
            + MONTH ( TODAY () )
        && MAX ( dCalender[DateInt] )
            > YEAR ( TODAY () ) * 12
                + MONTH ( TODAY () )
                - 12,
    "Last 12 Months",
    "Beyound last 12 Months"
)

Idea is to create a dynamic slicer to filter last 12 months data only page wide.

BUT in the column all the dates are showing up as "Beyound last 12 months" i.e. the condition is FALSE. What am i missing. or is there a diffferent way?

Thanks