cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Resolver III

## Previous Month before current month

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?.

1 ACCEPTED SOLUTION
Super User

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.

3 REPLIES 3
Super User

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.

Community Champion

Hi @Angel

Try using PREVIOUSMONTH function.

Cheers

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
Resolver III

Hi, @CheenuSing

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

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors