Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

0

Help in Building Formula

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.Screenshot 2022-01-18 154512.png

 

enterprise_unit_numberserial_numbercustomer_numberYearMonthMax HoursMax-Hours UpdatedNext Max HoursNext-Currentdiff_monthusage per month
1acn12020-04642664266602176288
1acn12020-06660266026710108254
1acn12020-0867106710678070235
1acn12020-1067806780678070135
1acn12020-1140356780678070135
1acn12020-1240376780678070135
1acn12021-01678067806780020
1acn12021-036310678069201401140
1acn12021-0469206920698262162
1acn12021-0569826982698262362
1acn12021-085168698276366542327
1acn12021-1076367636    
2bcn12020-04642664266602176288
2bcn12020-06660266026710108254
2bcn12020-0867106710678070235
Status: Delivered

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

Comments
v-robertq-msft
Community Support
Status changed to: Delivered

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