The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now
Hi Folks,
Can anyone help me to calculate usage/month based on the data I have below. The dataset is on serial_number level with the MaxHours for that month. The MaxHours should always increase with the months but we have few entries where MaxHours are lesser than the last month trend so we need to fix these by replacing the previous month values (as listed on Max-Hours Updated). Then there's another column "Next Max Hours" which shows the upcoming next hours for each month and serial number. The "Next-Current" column will simple calculate the difference between "Next Max Hours" and "Max-Hours Updated" but for the rows where we put the last month Max-Hours value in "Max-Hours Updated" it should also pick the last "Next-Current" value in order to eliminate the 0s. Then there's another column "diff_month" which just calculates the difference between Current row YearMonth and previous Month row in Months. And then there's last column which just divides the "Next-Current" with "diff_month" to calculate the usage/month for each row. You can see the required changes in higlighted cells.
enterprise_unit_number | serial_number | customer_number | YearMonth | Max Hours | Max-Hours Updated | Next Max Hours | Next-Current | diff_month | usage per month |
1 | a | cn1 | 2020-04 | 6426 | 6426 | 6602 | 176 | 2 | 88 |
1 | a | cn1 | 2020-06 | 6602 | 6602 | 6710 | 108 | 2 | 54 |
1 | a | cn1 | 2020-08 | 6710 | 6710 | 6780 | 70 | 2 | 35 |
1 | a | cn1 | 2020-10 | 6780 | 6780 | 6780 | 70 | 1 | 35 |
1 | a | cn1 | 2020-11 | 4035 | 6780 | 6780 | 70 | 1 | 35 |
1 | a | cn1 | 2020-12 | 4037 | 6780 | 6780 | 70 | 1 | 35 |
1 | a | cn1 | 2021-01 | 6780 | 6780 | 6780 | 0 | 2 | 0 |
1 | a | cn1 | 2021-03 | 6310 | 6780 | 6920 | 140 | 1 | 140 |
1 | a | cn1 | 2021-04 | 6920 | 6920 | 6982 | 62 | 1 | 62 |
1 | a | cn1 | 2021-05 | 6982 | 6982 | 6982 | 62 | 3 | 62 |
1 | a | cn1 | 2021-08 | 5168 | 6982 | 7636 | 654 | 2 | 327 |
1 | a | cn1 | 2021-10 | 7636 | 7636 | ||||
2 | b | cn1 | 2020-04 | 6426 | 6426 | 6602 | 176 | 2 | 88 |
2 | b | cn1 | 2020-06 | 6602 | 6602 | 6710 | 108 | 2 | 54 |
2 | b | cn1 | 2020-08 | 6710 | 6710 | 6780 | 70 | 2 | 35 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi,
According to your description, it seems that you want to get the help of creating calculated columns using DAX.
If so, I suggest you to go to the Power BI Desktop community so that the experts in the DAX can help you to create the correct DAX formula to solve your problem.
https://community.powerbi.com/t5/Desktop/bd-p/power-bi-designer
What’s more, this community is about the issues existing in the Power BI.
Thank you very much!
Best Regards,
Community Support Team _Robert Qin