Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Angel
Resolver III
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?.

 

Thanks in advance,

 

1 ACCEPTED SOLUTION
technolog
Super User
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.

View solution in original post

3 REPLIES 3
technolog
Super User
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.

CheenuSing
Community Champion
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!

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.