topic Re: Previous Month before current month in DAX Commands and Tips

Previous Month before current month

Angel — Tue, 10 Jan 2017 16:16:39 GMT

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,

Re: Previous Month before current month

Anonymous — Mon, 23 Jan 2017 05:28:08 GMT

Hi Angel

Try using PREVIOUSMONTH function.

Cheers

CheenuSing

Re: Previous Month before current month

Angel — Mon, 30 Jan 2017 11:52:49 GMT

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

Re: Previous Month before current month

technolog — Mon, 28 Aug 2023 20:45:22 GMT

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.