Variance between months, based on filtered table

[BowerPI]

Hello!

I'm new to PowerBI so any help would be appreciated 🙂

I have a source data table in PowerBi that looks something like this (Unfortunately can't share actual file due to confidentiality reasons):

This is just an example, but basically for a given site, there are multiple meter IDs, and each meter has a Room ID (room that it services).

I've managed to create a visual that summarises the total consumption of each RoomID at each month of the year for a given site.

I'm trying to find the variance month to month, and display it in a similar format for each site. E.g. based on the image directly above, to produce something like this:

Where ~3050% = (996226.55 - 31624.55)/31624.55 * 100% 

and

~ -3.75% = difference in consumption between May and June for Room ID: A010.

Will it require using DAX or creating a new table? If you know how to go about it please let me know 🙂

Thank you!

[Jihwan_Kim]

Hi,

Please check the below picture and the attached pbix file if it suits your requirement.

Consumption measure: = 
SUM( Data[Consumption] )

OFFSET function (DAX) - DAX | Microsoft Learn

compare to prev month measure: =
VAR _currentmonth = [Consumption measure:]
VAR _prevmonth =
    CALCULATE (
        [Consumption measure:],
        OFFSET (
            -1,
            ALL ( 'Calendar'[Year-Month sort], 'Calendar'[Year-Month] ),
            ORDERBY ( 'Calendar'[Year-Month sort], ASC )
        )
    )
RETURN
    IF (
        NOT ISBLANK ( _currentmonth ),
        IF (
            NOT ISBLANK ( _prevmonth ),
            FORMAT ( DIVIDE ( _currentmonth - _prevmonth, _prevmonth ), "#0.00%" ),
            FORMAT ( 0, "#0" )
        )
    )

[BowerPI]

Thanks for the reply. I am getting all 0% for my results but it may be due to my formatting of my "date" field. My date formatting is as follows: "Saturday, 30 April, 2022", "Tuesday, 31 May, 2022". How could I update your code above?

[Anonymous]

Hi @BowerPI ,

Please try to use this DAX to create a new column:

Column = 
VAR CURRENT_ROOM = 'Table'[Room ID]
VAR CURRENT_MONTH = 'Table'[Month].[MonthNo]
VAR PREVIOUS_MONTH = CURRENT_MONTH - 1
VAR PREVIOUS_consumption = 
CALCULATE(
    SUM('Table'[consumption]),
    FILTER(
        'Table',
        'Table'[Room ID] = CURRENT_ROOM && 'Table'[Month].[MonthNo] = PREVIOUS_MONTH
    )
)
RETURN
DIVIDE(('Table'[consumption] - PREVIOUS_consumption), PREVIOUS_consumption)

The final output is as below:

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[BowerPI]

Thanks for your response. I tried implementing the above, but I get the following error as you can see:

Here "RoomID" has been subbed in and "calc_kwhr" is just consumption. My date formatting is as follows: "Saturday, 30 April, 2022", "Tuesday, 31 May, 2022". How could I update your code above?

[Anonymous · Accepted solution]

Hi @BowerPI ,

Change the 

'dcs_test_data_2'[month].[Month]

into this:

'dcs_test_data_2'[month].[MonthNo]

[month].[Month] returns words for each month, and [month].[MonthNo] returns a number for each month

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[BowerPI]

Thanks for the suggestion. Another complication is that the months actually span over 3 years. When I use MonthNo. and check the output for current and previous month, I get very large numbers, where previous month no. is not equal to current month - 1.

There is something to do with the monthNo. but I can't figure out how it is working. Do you have any ideas?