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

Reply
kbebow
Regular Visitor

Grand total of datediff not correct

I have a simple statement: 
 
TimeToComplete = DATEDIFF([DateRaised],[DateCompleted],DAY). 
 
I am able to filter by employee and the TimeToComplete column is correct for each of the employee's entries.  However, the Grand Total is equal to the sum of all employees' TimeToComplete.  I need to have the Grand Total be only the sum of the selected employee.  What am I doing wrong?  Thanks.
2 ACCEPTED SOLUTIONS

Hi @kbebow ,
This would be your measure:

 

 

DateDiff = 
IF(HASONEVALUE('Table'[Start]);
    DATEDIFF(MIN('Table'[Start]);MIN('Table'[End]);DAY);
    SUMX(SUMMARIZE('Table';'Table'[Worker ID];'Table'[Start];'Table'[End];"Diff"; CALCULATE(DATEDIFF(MIN('Table'[Start]);MIN('Table'[End]);DAY)));[Diff])
)

 

 

As seen here:

 datediff2.jpg

File is here

Pls mark as solution if so. Thumbs up for the effort appreciated!

Groet, Steve. 

View solution in original post

Glad I could help, thumbs up  for the effort would be great. Thanks!

View solution in original post

8 REPLIES 8
lbendlin
Super User
Super User

please explain what "selected employee" means. Selected where? In a slicer?

What's the data model looking like?

Anonymous
Not applicable

Dear,

I have the same issue, please allow me to explain.

From a report with time logs I have created 3 measures
Time Clock IN = calculate(MIN(Raw_data[Time]),FILTER(Raw_data,Raw_data[Gebeurtenis]="AccessOK"&&Raw_data[Ingang gebied]="CLock IN"))
Time Clock OUT = calculate(MAX(Raw_data[Time]),FILTER(Raw_data,Raw_data[Gebeurtenis]="AccessOK"&&Raw_data[Ingang gebied]="Clock OUT UIT"))
Time diff = IFERROR(DATEDIFF([Time Clock IN],[Time Clock OUT],MINUTE)/60,0)
Each single row (date / employee) result for the measure Time worked is correct, only the total or DateToMonth is showing the numbers as expected.
 
Any help is much appreciated !Capture Time diff.JPG

 

you have not specified what you expect the total to show. Should it be the sum of the computed values?  In that case you need to use HASONEVALUE() or ISINSCOPE() to distinguish between individual items in the visual and the totals. 

 

For the totals you would then have to use a SUMX function. Technically you could also use SUMX for the individual items but it would be overkill since it would ony ever sum up one item.

Hi @kbebow ,
This would be your measure:

 

 

DateDiff = 
IF(HASONEVALUE('Table'[Start]);
    DATEDIFF(MIN('Table'[Start]);MIN('Table'[End]);DAY);
    SUMX(SUMMARIZE('Table';'Table'[Worker ID];'Table'[Start];'Table'[End];"Diff"; CALCULATE(DATEDIFF(MIN('Table'[Start]);MIN('Table'[End]);DAY)));[Diff])
)

 

 

As seen here:

 datediff2.jpg

File is here

Pls mark as solution if so. Thumbs up for the effort appreciated!

Groet, Steve. 

Great, thank you!

That worked - thank you so much!

Glad I could help, thumbs up  for the effort would be great. Thanks!

Anonymous
Not applicable

Meantime I figured out  how you can add up measures that are related to time stamps correctly.

1a) Original measure

Time Clock IN = calculate(MIN(Raw_data[Time]),FILTER(Raw_data,Raw_data[Gebeurtenis]="AccessOK"&&Raw_data[Ingang gebied]="CLock IN"))

2a) and then added another measure

Time Clock IN workable=if(countrows(values(DCalendar[Date]))=1,[Time Clock IN],sumx(values(DCalendar[Date]),[Time Clock IN]))

1b) Original measure
Time Clock OUT = calculate(MAX(Raw_data[Time]),FILTER(Raw_data,Raw_data[Gebeurtenis]="AccessOK"&&Raw_data[Ingang gebied]="Clock OUT UIT")) 

2b) and then added another measure

Time Clock IN Correct=if(countrows(values(DCalendar[Date]))=1,[Time Clock OUT],sumx(values(DCalendar[Date]),[Time Clock OUT]))

3) Calculate hours worked

Nett time = IFERROR(DATEDIFF([Time Clock IN Correct],[Time Clock OUT Correct],MINUTE)/60,0)

 

4)a Deviation to calculate overtime hours

Time diff=[Nett Time]-[ContractHours]

4b) and then added another measure

Overtime hours correct= if(countrows(values(DCalendar[Date]))=1,[Time diff],sumx(values(DCalendar[Date]),[Time diff]))

 

Steps "b" are resulting in correct sum up / grand total.

I am very aware that steps "a" are not necessary to create, as you can integrate them in step b.

It is just to show you the direction how I solved my issue.

 

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors