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 confidentialit...
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.

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.

      • BowerPI's avatar
        BowerPI
        Regular Visitor
        2 years ago

        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?