Calculate amount of minuts, with hours as columums

[Shpend_Mustafa]

I want to be able to calculate the number of minutes that have been spent for each hour.

Please check my example that I have created in excel

In power BI I have a Time table where each ID is unique. However, I do not know how to make the calculation of time used per hour. I really hope any of you can help me. Thanks in advance

[v-jingzhang · Accepted solution]

Hi @Shpend_Mustafa 

I create a sample file attached at bottom. Please download it to see details. 

The solution is a combination of Power Query and DAX. First use Power Query to transform the original table to get the minutes in every hour. Also add a table with all hours in a day.

Then create a relationship between two tables. Create a measure to get the number of minutes and make it return 0 for blank results. 

At last, use a Matrix visual to display the result. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[v-jingzhang · Accepted solution]

Hi @Shpend_Mustafa 

I create a sample file attached at bottom. Please download it to see details. 

The solution is a combination of Power Query and DAX. First use Power Query to transform the original table to get the minutes in every hour. Also add a table with all hours in a day.

Then create a relationship between two tables. Create a measure to get the number of minutes and make it return 0 for blank results. 

At last, use a Matrix visual to display the result. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[Shpend_Mustafa]

Thank you for this detailed explanation!
I'm very new to this and do not really understand how to succeed in writing the dax code.
I want to get exactly the same table that showed in your end result. 
do you want to be kind and show the code for how you created the calculations for each hour.
I hope it is not too much to ask.
Does this calculation take date change into account?

[v-jingzhang · Accepted solution]

Hi @Shpend_Mustafa 

This is calculated with Power Query M language instead of DAX. In Power BI Desktop, you can click "Transform data" to launch Power Query Editor. In Power Query Editor, you can select one query from the left-side Queries pane, then check what I have done to the query from the steps in APPLIED STEPS pane on the right. You can click the gear icon to see what is done in every step. The overall M code is available in Advanced Editor. You can use the user interface to perform transformation steps and their corresponding steps will be generated into M code automatically in Advanced Editor. You can also edit or write M code directly there when you have more experience.  

Reference: The Power Query user interface 

Best regards,

Jing