Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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!
Solved! Go to 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.
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" )
)
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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?
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.
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?
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.
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?
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
User | Count |
---|---|
86 | |
85 | |
68 | |
67 | |
63 |
User | Count |
---|---|
210 | |
118 | |
116 | |
81 | |
74 |