March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
Solved! Go to Solution.
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:
File is here.
Pls mark as solution if so. Thumbs up for the effort appreciated!
Groet, Steve.
Proud to be a Super User!
Awesome Keyboard Shortcusts in Power BI, thumbs up if you like the article
My Community Blog Articles (check them out!)
My Blog - Power M code to automatically detect column types -
How to create test data using DAX!
Glad I could help, thumbs up for the effort would be great. Thanks!
Proud to be a Super User!
Awesome Keyboard Shortcusts in Power BI, thumbs up if you like the article
My Community Blog Articles (check them out!)
My Blog - Power M code to automatically detect column types -
How to create test data using DAX!
please explain what "selected employee" means. Selected where? In a slicer?
What's the data model looking like?
Dear,
I have the same issue, please allow me to explain.
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:
File is here.
Pls mark as solution if so. Thumbs up for the effort appreciated!
Groet, Steve.
Proud to be a Super User!
Awesome Keyboard Shortcusts in Power BI, thumbs up if you like the article
My Community Blog Articles (check them out!)
My Blog - Power M code to automatically detect column types -
How to create test data using DAX!
Great, thank you!
That worked - thank you so much!
Glad I could help, thumbs up for the effort would be great. Thanks!
Proud to be a Super User!
Awesome Keyboard Shortcusts in Power BI, thumbs up if you like the article
My Community Blog Articles (check them out!)
My Blog - Power M code to automatically detect column types -
How to create test data using DAX!
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
26 | |
21 | |
20 | |
13 | |
12 |
User | Count |
---|---|
40 | |
27 | |
27 | |
21 | |
20 |