Differenza tra due date per calcolo tempo produttivo in Power BI

[Anonymous]

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

[AlB]

@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 

[AlB]

@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 

[Anonymous]

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?

[AlB]

@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 

[Anonymous]

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.

[AlB]

@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 

[Anonymous]

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. 

[AlB]

@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 

[Anonymous]

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?

[AlB]

@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 

[Anonymous]

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

[AlB]

@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 

[Anonymous]

this the Pbix example:

[Anonymous]

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

[AlB]

@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 

[Anonymous]

Can someone help me?

[Anonymous]

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

[AlB]

@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 

[Anonymous]

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

[AlB]

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