Forum Discussion

Angel's avatar
Angel
Icon for Resolver III rankResolver III
9 years ago
Solved

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' fo...
  • technolog's avatar
    3 years ago

    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.