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.
ho una tabella che contiene tra i vari campi anche data_inizio_lavorazione ('ddmmyy hh24:min:sec), data_fine_lavorazione ('ddmmyy hh24:min:sec').
quello che devo fare è calcolare il tempo di produzione che intercorre tra queste due date. il problema è:
1. possono essere anche due date di due giorni diversi
2. se faccio la differenza di queste due date mi conta un giorno normale di 24 ore
3. la macchina lavora 8 ore al giorno
4. devo escludere da questo calcolo gli eventuali sabati e domeniche.
come posso fare per calcolarmi questo tempo?
grazie
@Anonymous
In general I would recommend not to use the Auto Date/Time feature. It can cause unexpected problems. See https://community.powerbi.com/t5/Desktop/How-to-remove-Date-Hierarchy/td-p/647711
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
@Anonymous
Conceptually it is not difficult; you can follow the same logic we've used in the code above. It could be a bit tedious, though. Make sure you consider all the cases. Both these vars would need to be changed:
VAR time1stDay_ =
IF( dateLastDay_ <> date1stDay_,
MAX(0, ( date1stDay_ + ( 17 / 24 ) ) - MAX ( date1stDay_ + ( 9 / 24 ), Produzione[Orainizio] )),
MAX(0, ( MIN(Produzione[OraFine], date1stDay_ + ( 17 / 24 ) ) - MAX ( date1stDay_ + ( 9 / 24 ), Produzione[Orainizio] )))
)
VAR timeLastDay_ = IF ( dateLastDay_ = date1stDay_,
0,
MAX(0, MIN ( datelastDay_ + ( 17 / 24 ), Produzione[OraFine] ) - ( datelastDay_ + ( 9 / 24 ) ))
)
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
ok AIB, i think the file is working.
i changed the working hours because i saw that there are rows where "orainizio" is before 9 am and "orafine" is after 5 pm.
do you think that is difficult to split the working hours into 2 two shifts of work?
example: from 8 am to 12 pm and from 2 pm to 7 pm?
it's only a consideration... i think i should change this part of dax code.
IF( dateLastDay_ <> date1stDay_, MAX(0, ( date1stDay_ + ( 17 / 24 ) ) - MAX ( date1stDay_ + ( 9 / 24 ), Produzione[Orainizio] )), MAX(0, ( MIN(Produzione[OraFine], date1stDay_ + ( 17 / 24 ) ) - MAX ( date1stDay_ + ( 9 / 24 ), Produzione[Orainizio] )))
are you agree with me?
@Anonymous
Alberto, you are right. It needed some modifications. You seem to have changed the machine working hours to 8-18 in the code in the file? Instead of 9-17. Why?
Try this new version:
Result V2 =
VAR date1stDay_ = DATEVALUE ( Produzione[Orainizio] )
VAR dateLastDay_ = DATEVALUE ( Produzione[OraFine] )
VAR time1stDay_ =
IF( dateLastDay_ <> date1stDay_,
MAX(0, ( date1stDay_ + ( 17 / 24 ) ) - MAX ( date1stDay_ + ( 9 / 24 ), Produzione[Orainizio] )),
MAX(0, ( MIN(Produzione[OraFine], date1stDay_ + ( 17 / 24 ) ) - MAX ( date1stDay_ + ( 9 / 24 ), Produzione[Orainizio] )))
)
VAR timeLastDay_ = IF ( dateLastDay_ = date1stDay_,
0,
MAX(0, MIN ( datelastDay_ + ( 17 / 24 ), Produzione[OraFine] ) - ( datelastDay_ + ( 9 / 24 ) ))
)
VAR workingDaysInBetween_ =
COUNTROWS (
FILTER (
ALL ( 'Date' ),
'Date'[Date] > date1stDay_ && 'Date'[Date] < dateLastDay_ && 'Date'[isWorkingDay] = TRUE ()
)
)
RETURN
24 * ( time1stDay_ + (workingDaysInBetween_ * ( 8 / 24 )) + timeLastDay_ )
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
sorry for the misunderstanding.
here the most updated file. with new tabe Date and the column (result) with the script that you have sent me before.
https://www.dropbox.com/s/3bmwdvx3hjhge4h/DASHBOARD%20PRODUZIONE%20-%20Copia.pbix?dl=0
thanks you so much for your time.
@Anonymous
I'm pretty sure it works, as is or with very minor changes.
The dates you show are not in the file you shared initially.
It doesn't help me if you create the date table and do not share the new file with it, or if you show the code in a screen cap that cannot be copied.
Please make my work easier. Otherwise I won't be able to spend any more time on this.
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
i think this result is not right.
i tried to do this solution but the result isn't correct. the difference between the 2 dates is wrong.
i also triedto change the column format but it doesn't work.
and this is the table that i've created. here i can mark the days as a working days.
@Anonymous
It would be something like:
New Col =
VAR date1stDay_ = DATEVALUE ( Produzione[Orainizio] )
VAR dateLastDay_ = DATEVALUE ( Produzione[OraFine] )
VAR time1stDay_ = MAX(0, ( date1stDay_ + ( 17 / 24 ) ) - MAX ( date1stDay_ + ( 9 / 24 ), Produzione[Orainizio] ))
VAR timeLastDay_ =
IF (
dateLastDay_ = date1stDay_,
0,
MAX(0, MIN ( datelastDay_ + ( 17 / 24 ), Produzione[OraFine] ) - ( datelastDay_ + ( 9 / 24 ) ))
)
VAR workingDaysInBetween_ =
COUNTROWS (
FILTER (
ALL ( DateT ),
DateT[Date] > date1stDay_ && DateT[Date] < dateLastDay_ && DateT[isWorkingDay] = TRUE ()
)
)
RETURN
24 * ( time1stDay_ + (workingDaysInBetween_ * ( 8 / 24 )) + timeLastDay_ )
This will give you the reuslt in hours. You can then convert it to the format you prefer
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
ok i'll add this table in the model, but how can i calcuate the the time (ex: 1h, 2h:32min,...) and not the days between the two dates?
however i cannot change the data source, these are the tables that i have, do you think i could merge something?
@Anonymous
Thanks. I understand now. You need a Date table with the working days flagged (columns that flag holidays and weekends). Where is that in the model? If you don't have one, you need to build it.
Why are you using all those bidirectional relationships?? They should all have one direction only (unless you have a very good reason fro the contrary and know very well what you are doing)
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
i would like the difference betwan the column with this condition:
1. exclude weekend
2. consider only 8h/day.
example of row:
program: abc
orainizio: 18/09/20 15:32 pm (for example Friday)
orafine: 21/09/20 09:05 am (for example Monday)
expected result: (1h:28min + 0h:05min) = 1h33min
considertion:
19/09 and 20/09 is weekend days. so i don't consider these days in the calculated colum. moreover the machine can works 8h/day from 9 am to 5 pm so in the fist day (18/09) the total that the machine has worked is 1:28 minutes and the 21/09 the machine has started to work ad 9 am and has finished at 9:05 am (orafine) so 0h:05min and the expected result is 1h:33.
i tried to be clearer
Alberto
@Anonymous
So what is the expected result in your example above?? I understand you want a calculated column in that table. If so, what values would it have in the example? So that I can be completely clear on what the requirements are. Grazie
programma |
Ora inizio |
Ora fine | Expected result |
ABC-Program | 21/01/2020 08:00:00 | 22/01/2020 09:30:00 | ???? |
DEF-Program | 22/01/2020 10:00:00 | 27/01/2020 12:45:00 | ???? |
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
this the Pbix example:
hi AIB,
how are you?
the problem is that a cannot calculate the difference fìbetween 2 dates and also exclude the weekend.
for example:
from the "Produzione" table
programma | Ora inizio | Ora fine |
ABC-Program | 21/01/2020 08:00:00 | 22/01/2020 09:30:00 |
DEF-Program | 22/01/2020 10:00:00 | 27/01/2020 12:45:00 |
i want to precisate that:
1. i need the difference between second and third columns
2. the machine can works 8h/day (9-13/14-18)
3. in the second row between the 2 dates there are 2 free days (saturday and sunday) and i need to exclude that.
thanks for the support
Alberto
@Anonymous
Sorry, I must have missed your previous message.
Now that I have the pbix, can you please explain again what you need with an example based on the data in the file you've shared. So that it is clear. I do not see a column "ata_inizio_lavorazione" in the file, for instance
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Can someone help me?
hi @AlB ,
thank you for your time.
the problem is that i can't calculate the time between two date. i need to exclude the weekend and the hours that the machine is off.
attached the example of the file that i normally use.
i'm not able to upload the PBIX file.
if you send me your email, i would like to send the PBIX to you.
tnks
Alberto
@Anonymous
You can share here the URL to the file hosted elsewhere: Dropbox, Onedrive... or just upload the file to a site like tinyupload.com (no sign-up required). OR you send me the URL by private message here on the platform
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Ok this is the PBIX file
https://www.dropbox.com/s/2ucn15ghevpxwtd/DASHBOARD%20PRODUZIONE.pbix?dl=0
write me at albertosimonetto95@gmail.com for any clarification.
tnks
Alberto
Hi @Anonymous
Can you share the pbix so that we can run a couple of tests? Or at least a sample of your data and show an example based on that data with the expected result
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
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 |
---|---|
46 | |
26 | |
21 | |
11 | |
8 |
User | Count |
---|---|
77 | |
50 | |
46 | |
16 | |
12 |