Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Dear All Folks,
I need your help to calculate usage in PowerBI as per desired results. My table has four columns Serial Number, Year, Month and Max Hours, I want to calculate the monthly usage based on Max Hours and Month column. The formula to calculate Usage to get the desired results is listed on Formula column but I don't know how to do this in PowerBI. I am actually taking the difference between Max of (Max Hours column) and Min(Max Hours) column and dividing this with Date difference in Month to get the usage/month. Please see attached screenshot for the data I have and the desired output(Usage).
Solved! Go to Solution.
Hi, @hamzashafiq
You can calculated columns as below:
YearMonth = DATE('Table'[Year],'Table'[Month],1)
Start_date =
CALCULATE (
MAX ( 'Table'[YearMonth] ),
FILTER (
'Table',
'Table'[Max Hours] <> BLANK ()
&& 'Table'[YearMonth] <= EARLIER ( 'Table'[YearMonth] )
&& 'Table'[Serial Number] = EARLIER ( 'Table'[Serial Number] )
)
)
Diff =
VAR MaxHours =
IF (
'Table'[Max Hours] <> BLANK (),
CALCULATE (
MIN ( 'Table'[Max Hours] ),
FILTER (
'Table',
'Table'[Serial Number] = EARLIER ( 'Table'[Serial Number] )
&& 'Table'[Max Hours] > EARLIER ( 'Table'[Max Hours] )
)
),
BLANK ()
)
VAR MinHours = 'Table'[Max Hours]
RETURN
IF ( MaxHours = BLANK (), BLANK (), MaxHours - MinHours )
Usage =
VAR Max_hours =
LOOKUPVALUE (
'Table'[Diff],
'Table'[Serial Number], 'Table'[Serial Number],
'Table'[YearMonth], 'Table'[Start_date]
)
VAR Count_month =
CALCULATE (
COUNT ( 'Table'[Start_date] ),
ALLEXCEPT ( 'Table', 'Table'[Serial Number], 'Table'[Start_date] )
)
RETURN
Max_hours / Count_month
Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @hamzashafiq
I may have misunderstood what you meant before. From your last screenshot, the value of some months (3,4,5,6,7,11,12) is missing rather than 'blank'. If so, please check my new attachment. Hope this could help.
Best Regards,
Community Support Team _ Eason
@v-easonf-msft Nope same issue, I am also getting invalid value in Start_date column. Please see the below screenshot.
Hi, @hamzashafiq
I may have misunderstood what you meant before. From your last screenshot, the value of some months (3,4,5,6,7,11,12) is missing rather than 'blank'. If so, please check my new attachment. Hope this could help.
Best Regards,
Community Support Team _ Eason
Hey,
It's me again, the previous solution you gave was perfect except for the cases when we have null values in "Max-Hours" column or the values become less compared with previous month or row (highlighted in yellow). For these cases we need to replace the values for the previous month Hours (highlighted in purple). Please see the attached screenshot and also the desired input for "Max-Hours" column.
Below is the formula I changed but it only replaces for one row only.
Hey,
It's me again, the previous solution you gave was perfect except for the cases when we have null values in "Max-Hours" column or the values become less compared with previous month or row (highlighted in yellow). For these cases we need to replace the values for the previous month Hours (highlighted in purple). Please see the attached screenshot and also the desired input for "Max-Hours" column.
Below is the formula I changed but it only replaces for one row only.
I am getting this error.
Hi, @hamzashafiq
Try to add an extra set of parameters to the variable Max_Hours in the formula 'Usage', similar to the following :
VAR Max_hours =
LOOKUPVALUE (
'Table'[Diff],
'Table'[Serial Number], 'Table'[Serial Number],
'Table'[customer_Number], 'Table'[customer_Number], //***
'Table'[YearMonth], 'Table'[Start_date]
)
Best Regards,
Community Support Team _ Eason
Hi, @hamzashafiq
You can calculated columns as below:
YearMonth = DATE('Table'[Year],'Table'[Month],1)
Start_date =
CALCULATE (
MAX ( 'Table'[YearMonth] ),
FILTER (
'Table',
'Table'[Max Hours] <> BLANK ()
&& 'Table'[YearMonth] <= EARLIER ( 'Table'[YearMonth] )
&& 'Table'[Serial Number] = EARLIER ( 'Table'[Serial Number] )
)
)
Diff =
VAR MaxHours =
IF (
'Table'[Max Hours] <> BLANK (),
CALCULATE (
MIN ( 'Table'[Max Hours] ),
FILTER (
'Table',
'Table'[Serial Number] = EARLIER ( 'Table'[Serial Number] )
&& 'Table'[Max Hours] > EARLIER ( 'Table'[Max Hours] )
)
),
BLANK ()
)
VAR MinHours = 'Table'[Max Hours]
RETURN
IF ( MaxHours = BLANK (), BLANK (), MaxHours - MinHours )
Usage =
VAR Max_hours =
LOOKUPVALUE (
'Table'[Diff],
'Table'[Serial Number], 'Table'[Serial Number],
'Table'[YearMonth], 'Table'[Start_date]
)
VAR Count_month =
CALCULATE (
COUNT ( 'Table'[Start_date] ),
ALLEXCEPT ( 'Table', 'Table'[Serial Number], 'Table'[Start_date] )
)
RETURN
Max_hours / Count_month
Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |