Search code examples
powerbidaxpowerbi-desktoppowerbi-custom-visuals

How to calculate MTD of previous month

I've calculated a MoM variance thanks to this formula:

costInBillingCurrency MoM% = 
IF(
    ISFILTERED('Usage details'[date]),
    ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
    VAR __PREV_MONTH =
        CALCULATE(
            SUM('Usage details'[costInBillingCurrency]),
            DATEADD('Usage details'[date].[Date], -1, MONTH)
        )
    RETURN
        DIVIDE(
            SUM('Usage details'[costInBillingCurrency]) - __PREV_MONTH,
            __PREV_MONTH
        )
)

But in order to make it work I need to use 2 slicers, one on the year and one on the month: enter image description here

The problem comes when it's mid February and I'm comparing the numbers of the previous month.

This means that I'm comparing, let's say, 15 days of February with 31 days of January.

What I would like to do is to compare the first 15 days of February with the first 15 days of January

If I try to use the Power BI Time Slicer everything brakes.

So I searched for an alternative online and I found this:

But they don't work.

How can I compare the first 15 days of this month with the first 15 days of the last month?

Or the first week of this month against the first week of the last month?

Solution

  • I found my own way:

    First create Last Month:

    Last Month = 
CALCULATE(
    SUM('Usage details'[costInBillingCurrency]), 
    DATEADD(
        FILTER(DATESMTD('Usage details'[date].[Date]), 'Usage details'[date].[Date] < TODAY()),
        -1,
        MONTH))

    Then create MoM Same Period Last Month:

    MoM Same Period Last Month = 

VAR _date = DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY()))
VAR _maxDate = MAX('Usage details'[date].[Date])
VAR _isEndOfMonth = CALCULATE(MAX('Usage details'[date].[Date]), ALLSELECTED('Usage details')) == _date
VAR _compareDate = 
IF(
    _isEndOfMonth,
    _maxDate,
    MIN(DATE(YEAR(_maxDate), MONTH(_maxDate), DAY(_date)), _maxDate)
)

RETURN
CALCULATE([Last Month],
    DATESMTD(
        CALCULATETABLE(
            VALUES('Usage details'[date].[Date]),
            KEEPFILTERS('Usage details'[date] <= _compareDate))))