Create Measure / Column with IF using schedule.

[vitexo87]

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.

[gpiero · Accepted solution]

@vitexo87

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 )
    )
))

[vitexo87]

@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]

@vitexo87

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?

[vitexo87]

@gpiero

I have the following graphic:

As I went up it makes this calculation of losses.

And as you can see the hours presented are 3 shifts, but it is necessary to display information only from one shift that is the current shift.

I need now to present in this chart only the losses of my current shift and that is where the calculated column that you passed me enters.

How can I apply the calculated column that you passed me within my measurement that calculates the amount of losses?

[gpiero]

@vitexo87

X-axis should linked to shift 1/2/3 not to CurrentTime.

Then you can add a visual that allow you to filter by shift

[vitexo87]

@gpiero

Yes in modeling the data is already related but I can not apply the rule that I need in a filter in the graph, but in the measure that I have passed.

How could I apply the calculated column that you passed me as far as I could?

[gpiero]

Do you mean like this?

test

[vitexo87]

@gpiero

Yes it is almost that, instead of showing the three shifts (1, 2, 3) I want to show only one shift, which is happening now and on the X axis would be the hours for that shift.