last 3 months without counting the current month

[kasife]

Hi Guys,

I'm using a measure to know the accumulated result of the last 3 months, but I would like to get the result of the last 3 months without counting the current month. How could I do this?

My measure:

 CALCULATE(
        [_Total vendas],
        DATESINPERIOD(d_calendario[Date],MAX(d_calendario[Date]),-3,MONTH))

 The result I want to get:

[MohammadLoran25 · Accepted solution]

Hiii,

If you search and read the explanations for STARTOFMONTH and DATESINPERIOD Functions, you can get the result you are looking for by measure below:

CALCULATE (
    [_Total Vendas],
    DATESINPERIOD (
        d_calendario[Date],
        STARTOFMONTH ( DATEADD ( d_calendario[Date], -3, MONTH ) ),
        +2,
        MONTH
    )
)

Notice that it is more general than the thing you wanted. Actually it calculates the previous 3months per month (for example if your month is Dec, then it calculates over Sept, Oct, Nov).

So If you only want to calculate previous 3months based on current month, you need to change it accordingly.

Regards,

Loran

[MohammadLoran25 · Accepted solution]

Hiii,

If you search and read the explanations for STARTOFMONTH and DATESINPERIOD Functions, you can get the result you are looking for by measure below:

CALCULATE (
    [_Total Vendas],
    DATESINPERIOD (
        d_calendario[Date],
        STARTOFMONTH ( DATEADD ( d_calendario[Date], -3, MONTH ) ),
        +2,
        MONTH
    )
)

Notice that it is more general than the thing you wanted. Actually it calculates the previous 3months per month (for example if your month is Dec, then it calculates over Sept, Oct, Nov).

So If you only want to calculate previous 3months based on current month, you need to change it accordingly.

Regards,

Loran

[kasife]

@MohammadLoran25 How would this measurement look from a selected date? Can you help me?

[MohammadLoran25]

@kasife 

What do you mean exactly?

As I said it is different per month. For example if the month in your table row is Dec, then the previous 3 months would be Sept, Oct, Nov in order.

In the above-mentioned example, the statement below:

STARTOFMONTH ( DATEADD ( d_calendario[Date], -3, MONTH ) )

would be the first day of Sept

then through:

DATESINPERIOD(...., +2,Month)

It would include the Sept, Oct and November that finally filter d_calendario[Date]

[kasife]

@MohammadLoran25 

I tried to demonstrate in the image the result I would like:

[MohammadLoran25]

I got what you need.

The measure that I wrote works in this case as well.

Is there anything wrong with that?

[kasife]

@MohammadLoran25 The measure is returning the last 3 months always starting from the current month.

[MohammadLoran25 · Accepted solution]

Lets do it completely in another way.

1-In your DateTable, Create a calculated column as below:

YearMonthIndex=YEAR(DateTable[Date])*12+MONTH(DateTable[Date])

It gives you the index for combinations of year and month.

2-Then Create your Previous 3 Months measure as this:

CALCULATE (
    [_Total Vendas],
    FILTER (
        ALL ( DateTable ),
        DateTable[YearMonthIndex]
            >= MAX ( DateTable[YearMonthIndex] ) - 3
            && DateTable[YearMonthIndex]
                <= MAX ( DateTable[YearMonthIndex] ) - 1
    )
)

If it solves your problem, then please consider Accepting it as the solution
Regards,
Loran

[kasife]

@MohammadLoran25  It worked perfectly. Thank you very much 🙂

[kasife]

Thanks so much @MohammadLoran25