Previous Month before current month

[Angel]

Hi, everybody

I need a phormula that calculates a value of the previous month before the current one.

I don't want to use slicers to select the month. There has to be something 'invisible' for end user.

I have tried this:  REVPAR previous month = CALCULATE(SUM('REVPAR/REVPAG'[Amount)/SUM('REVPAR/REVPAG'[Capacity]);FILTER(DimDate;DimDate[Month]=MONTH(TODAY())-1);FILTER(DimDate;DimDate[Year]=YEAR(TODAY())))

As you can see, it doesn't work because current month is Jan-2017 and the phormula refers to Dic-2017. 

Any tip?.

Thanks in advance,

[technolog · Accepted solution]

The issue with your formula is when the current month is January (i.e., MONTH(TODAY()) = 1). Subtracting 1 directly from the month gives December (i.e., MONTH(TODAY()) - 1 = 0), but it also fails to adjust the year backwards, hence you get Dec-2017 instead of Dec-2016.

You need to account for the change in year when you try to get the previous month from January.

Here's a way to handle this:

REVPAR previous month =
VAR PrevMonth = EDATE(TODAY(), -1)
RETURN
CALCULATE(
SUM('REVPAR/REVPAG'[Amount]) / SUM('REVPAR/REVPAG'[Capacity]),
FILTER(
DimDate,
DimDate[Month] = MONTH(PrevMonth) && DimDate[Year] = YEAR(PrevMonth)
)
)
Let's break down the formula:

The EDATE function is used to get the exact date one month before today. This function will handle the year transition seamlessly. So, if the current month is January 2017, PrevMonth will be set to December 2016.
The main calculation then sums the values for 'REVPAR/REVPAG'[Amount] and divides by the sum of 'REVPAR/REVPAG'[Capacity] for that particular month and year of PrevMonth.
With this formula, there's no need for slicers or user input, and it will automatically calculate values for the month just prior to the current month. Adjust the formula as needed based on your actual table and column names if they differ from the ones provided.

[Anonymous]

Hi @Angel

Try using PREVIOUSMONTH function.

Cheers

CheenuSing

[Angel]

Hi, @Anonymous

I think if I use PreviusMonth i must select a month in slicer. This is not the thing I want to do.

I want to show previousmonth before current without select month in a slicer.

It must be dynamicilly,  without user interaction.

Anyway, I have created a boolean column that indicates if sales date is in the previusmonth.

If I filter by true, It works.

Thanks, anyway