Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Create Measure / Column with IF using schedule.

I need to create a measure or field in which depending on the current schedule of my system it shows in which shift we are, for example:

 

Today we have the 24 hours divided into 3 shifts:
Turn A starts at 06:00:01 AM and ends at 02:00:00 PM
Turn B starts at 02:00:01 PM and ends at 09:00:00 PM
Turn C Starts at 09:00:01 PM and ends at 06:00:00 AM

 

The logic would be something like: if CURRENT TIME is between 06:00:01 AM and 02:00:00 PM then display TURN A.

 

I was thinking about creating a case in the where of my T-SQL but I think it's better to do this already in Power BI.

 

Any suggestions on how I could create this measure or column in Power BI?

 

Thank you.

Need Help
  • gpiero's avatar
    gpiero
    9 years ago

    Anonymous

     

    This could help you in case of calculated column?

     

    CalcShift_ = 
/* Shift 1 6:00:00 - 13:59:59*/
/* Shift 2 14:00:00 - 21:59:59*/
/* Shift 3 22:00:00 - 05:59:59*/
IF (
    HOUR ( [CurrentTIme] ) >= 6
        && HOUR ( [CurrentTIme] ) <= 13;

    1;
    IF (
        HOUR ( [CurrentTIme] ) >= 14
            && HOUR ( [CurrentTIme] ) <= 21;
        2;
        IF ( HOUR ( [CurrentTIme] ) >= 22 && HOUR ( [CurrentTIme] ) <= 23; 3;
IF ( HOUR ( [CurrentTIme] ) >= 0 && HOUR ( [CurrentTIme] ) <= 5; 3; 0 )
    )
))

     

8 Replies

  • gpiero's avatar
    gpiero
    Skilled Sharer
    9 years ago

    Anonymous

     

    This could help you in case of calculated column?

     

    CalcShift_ = 
/* Shift 1 6:00:00 - 13:59:59*/
/* Shift 2 14:00:00 - 21:59:59*/
/* Shift 3 22:00:00 - 05:59:59*/
IF (
    HOUR ( [CurrentTIme] ) >= 6
        && HOUR ( [CurrentTIme] ) <= 13;

    1;
    IF (
        HOUR ( [CurrentTIme] ) >= 14
            && HOUR ( [CurrentTIme] ) <= 21;
        2;
        IF ( HOUR ( [CurrentTIme] ) >= 22 && HOUR ( [CurrentTIme] ) <= 23; 3;
IF ( HOUR ( [CurrentTIme] ) >= 0 && HOUR ( [CurrentTIme] ) <= 5; 3; 0 )
    )
))

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      gpiero

       

      Thank you it worked.


        Now there has arisen a need to apply this measure, which I have created in a new measure where I make the following calculation:


      UnitsBoats (Unit) L1_CFA = CALCULATE (IF (ISBLANK (CALCULATE ( SUM ('IndicatorValue' [Value]);
      TREATAS ({"UnitsBoas (Unit)")}; 'Indicator' [Description]); Equipment [Description] = "L1_CFA")); 0;

      CALCULATE (SUM ('IndicatorValue' [Value]); TREATAS ({"UnitsBoas (Unit)")}; 'Indicator' [Description])
      ; Equipment [Description] = "L1_CFA")))

       

      The goal would be to use the value of the measure that you passed me as a filter, would that be possible?

      • gpiero's avatar
        gpiero
        Skilled Sharer
        9 years ago

        Anonymous

         

        I think I did not catch the point.

        I did not passed you a measure but a calculated column and eachr row contains it own value.

         

        Do you mean how to use  that value to display  which equipment has been required/given back  in a specific shift?