Forum Discussion

BowerPI's avatar
BowerPI
Regular Visitor
2 years ago
Solved

Variance between months, based on filtered table

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!

Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      BowerPI
      Regular Visitor
      2 years ago

      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's avatar
        Anonymous
        Not applicable
        2 years ago

        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.

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    2 years ago

    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's avatar
      BowerPI
      Regular Visitor
      2 years ago

      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?