DAX measure calculate hours for each day

[Anonymous]

Hi Community! 

I have a report I'm working on, and I need to calculate the hours that were logged every day. 

The problem I'm having is that the hours seem to calculate alright for each day in the data view. 

It calculates per employee the number of hours worked on a certain day. 

However, when I drag hours into the view it automatically aggregates it to month: 

I tried to make a measure called CorrectBillableHours that looks the following: 

CALCULATE(MIN(BillableHours[Hours]); Date[FullDate])  but this just sums the hours to 160 for every date as you can see in the example. 

How can I fix this that it just shows the amount of Hours worked for each day? 

Is it a data model problem? Am I using a wrong measure?    

Thanks for the help in advance. 

[v-yingjl · Accepted solution]

Hi @Anonymous ,

Since there is no specific PBIX file，I have tried to select part of data and provide two ways to solve your problems:

1.You can create the following measure directly to calculate the hours that were logged every day:

         Total = CALCULATE(SUM('BillableHours'[Hours]) ,FILTER('BillableHours','BillableHours'[DateKey] in FILTERS('Date'[FullDate])))

You will get the result like this:

  

2.Create relationships between two tables but you should change the Datekey’s data type first:

Select Edit Queries and in Transform plate, choose Datekey column and change the data type into date, close and apply Edit Queries

Then in Home plate, select Manage Relationships, create relationships between two tables when you select Datekey column and FullDate column

Finally choose a table visual and put FullDate column and Hours column in it, you will get the result like this:

Here is the demo , please try it:

PBIX 

Best Regards,

Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

just right click on column and click on "Don't summerize".

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

[Anonymous]

I've tried that but it still gives the same result. 

[Anonymous]

share your pbix.