Calculation with Time Intelligence

[Coryanthony]

Hello, I need help with the following measure.

I have a measure that calculates "Allowed $" based on hours worked. We updated the "Allowed $" for the new year. I need to edit the measure to calculate the old Allowed $  for dates prior to 12/31/2022 and calculate the new allowed amount for dates on/after 1/1/2023.

Please see my current measure. Thank you.

Allowed OT $ =

SumX(SUMMARIZE(

        Timesheet,

        Timesheet[Employee ID],Timesheet[Date],Timesheet[Holiday],Timesheet[Day of Week],

        "DailyHrs",sum(Timesheet[Number (unit)])),

    SWITCH(TRUE,

        [DailyHrs]>=4 && [DailyHrs]<8 && Timesheet[Holiday] = "Yes" ,12,

        [DailyHrs]>=8 && Timesheet[Holiday] = "Yes" ,32,

        [DailyHrs]>=10 &&'Timesheet'[Day of Week]<=4,20,

        [DailyHrs]>=4 && [DailyHrs]<8 && 'Timesheet'[Day of Week]>=5,12,

        [DailyHrs]>=8 && 'Timesheet'[Day of Week]>=5,32,

        0

        ))

[ppm1 · Accepted solution]

Please try this combined measure. 

NewMeasures =
VAR afterdate =
    DATE ( 2023, 1, 1 )
VAR after =
    CALCULATE (
        SUMX (
            ADDCOLUMNS (
                SUMMARIZE (
                    Timesheet,
                    Timesheet[Employee ID],
                    Timesheet[Date],
                    Timesheet[Holiday],
                    Timesheet[Day of Week]
                ),
                "DailyHrs", CALCULATE ( SUM ( Timesheet[Number (unit)] ) )
            ),
            SWITCH (
                TRUE,
                [DailyHrs] >= 4
                    && [DailyHrs] < 8
                    && Timesheet[Holiday] = "Yes", 18,
                [DailyHrs] >= 8
                    && Timesheet[Holiday] = "Yes", 45,
                [DailyHrs] >= 10
                    && 'Timesheet'[Day of Week] <= 4, 45,
                [DailyHrs] >= 4
                    && [DailyHrs] < 8
                    && 'Timesheet'[Day of Week] >= 5, 18,
                [DailyHrs] >= 8
                    && 'Timesheet'[Day of Week] >= 5, 45,
                0
            )
        ),
        Timesheet[Date] >= afterdate
    )
VAR before =
    CALCULATE (
        SUMX (
            ADDCOLUMNS (
                SUMMARIZE (
                    Timesheet,
                    Timesheet[Employee ID],
                    Timesheet[Date],
                    Timesheet[Holiday],
                    Timesheet[Day of Week]
                ),
                "DailyHrs", CALCULATE ( SUM ( Timesheet[Number (unit)] ) )
            ),
            SWITCH (
                TRUE,
                [DailyHrs] >= 4
                    && [DailyHrs] < 8
                    && Timesheet[Holiday] = "Yes", 12,
                [DailyHrs] >= 8
                    && Timesheet[Holiday] = "Yes", 32,
                [DailyHrs] >= 10
                    && 'Timesheet'[Day of Week] <= 4, 20,
                [DailyHrs] >= 4
                    && [DailyHrs] < 8
                    && 'Timesheet'[Day of Week] >= 5, 12,
                [DailyHrs] >= 8
                    && 'Timesheet'[Day of Week] >= 5, 32,
                0
            )
        ),
        Timesheet[Date] < afterdate
    )
RETURN
    after + before

Pat 

[v-tangjie-msft]

Hi @Coryanthony ,

According to your requirements description, you want to achieve the purpose of merging into one measure according to the logic of [date BEFORE/After 1/1/2023] according to the two measures provided in the reply above, right?

If this is the case, you might consider trying this measure to logically combine two measures using the If() function.

Allowed OT $ =
If(Max(Timesheet[Date])<Date(2023,1,1),
SumX(SUMMARIZE(
        Timesheet,
        Timesheet[Employee ID],Timesheet[Date],Timesheet[Holiday],Timesheet[Day of Week],
        "DailyHrs",sum(Timesheet[Number (unit)])),
    SWITCH(TRUE,
        [DailyHrs]>=4 && [DailyHrs]<8 && Timesheet[Holiday] = "Yes" ,12,
        [DailyHrs]>=8 && Timesheet[Holiday] = "Yes" ,32,
        [DailyHrs]>=10 &&'Timesheet'[Day of Week]<=4,20,
        [DailyHrs]>=4 && [DailyHrs]<8 && 'Timesheet'[Day of Week]>=5,12,
        [DailyHrs]>=8 && 'Timesheet'[Day of Week]>=5,32,
        0
        ))
,
SumX(SUMMARIZE(
        Timesheet,
        Timesheet[Employee ID],Timesheet[Date],Timesheet[Holiday],Timesheet[Day of Week],
        "DailyHrs",sum(Timesheet[Number (unit)])),
    SWITCH(TRUE,
        [DailyHrs]>=4 && [DailyHrs]<8 && Timesheet[Holiday] = "Yes" ,18,
        [DailyHrs]>=8 && Timesheet[Holiday] = "Yes" ,45,
        [DailyHrs]>=10 &&'Timesheet'[Day of Week]<=4,45,
        [DailyHrs]>=4 && [DailyHrs]<8 && 'Timesheet'[Day of Week]>=5,18,
        [DailyHrs]>=8 && 'Timesheet'[Day of Week]>=5,45,
        0
        ))
)

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[Ashish_Mathur]

Hi,

Share some data (in a format that can be pasted in an MS Excel file), explain the question and show the expected result.

[ppm1]

Switching to the ADDCOLUMNS(SUMMARIZE pattern should help, but the SWITCH evaluation on every row may still keep things slow.

Allowed OT $ =
SUMX (
    ADDCOLUMNS (
        SUMMARIZE (
            Timesheet,
            Timesheet[Employee ID],
            Timesheet[Date],
            Timesheet[Holiday],
            Timesheet[Day of Week]
        ),
        "DailyHrs", CALCULATE ( SUM ( Timesheet[Number (unit)] ) )
    ),
    SWITCH (
        TRUE,
        [DailyHrs] >= 4
            && [DailyHrs] < 8
            && Timesheet[Holiday] = "Yes", 12,
        [DailyHrs] >= 8
            && Timesheet[Holiday] = "Yes", 32,
        [DailyHrs] >= 10
            && 'Timesheet'[Day of Week] <= 4, 20,
        [DailyHrs] >= 4
            && [DailyHrs] < 8
            && 'Timesheet'[Day of Week] >= 5, 12,
        [DailyHrs] >= 8
            && 'Timesheet'[Day of Week] >= 5, 32,
        0
    )
)

Pat

[Coryanthony]

Hi @Ashish_Mathur and @ppm1 

To simplify: I need below measure to reflect on date BEFORE 1/1/2023.

Allowed OT $ =

SumX(SUMMARIZE(

        Timesheet,

        Timesheet[Employee ID],Timesheet[Date],Timesheet[Holiday],Timesheet[Day of Week],

        "DailyHrs",sum(Timesheet[Number (unit)])),

    SWITCH(TRUE,

        [DailyHrs]>=4 && [DailyHrs]<8 && Timesheet[Holiday] = "Yes" ,12,

        [DailyHrs]>=8 && Timesheet[Holiday] = "Yes" ,32,

        [DailyHrs]>=10 &&'Timesheet'[Day of Week]<=4,20,

        [DailyHrs]>=4 && [DailyHrs]<8 && 'Timesheet'[Day of Week]>=5,12,

        [DailyHrs]>=8 && 'Timesheet'[Day of Week]>=5,32,

        0

        ))

And below measure to reflect on dates On/AFTER 1/1/2023

Allowed OT $ =

SumX(SUMMARIZE(

        Timesheet,

        Timesheet[Employee ID],Timesheet[Date],Timesheet[Holiday],Timesheet[Day of Week],

        "DailyHrs",sum(Timesheet[Number (unit)])),

    SWITCH(TRUE,

        [DailyHrs]>=4 && [DailyHrs]<8 && Timesheet[Holiday] = "Yes" ,18,

        [DailyHrs]>=8 && Timesheet[Holiday] = "Yes" ,45,

        [DailyHrs]>=10 &&'Timesheet'[Day of Week]<=4,45,

        [DailyHrs]>=4 && [DailyHrs]<8 && 'Timesheet'[Day of Week]>=5,18,

        [DailyHrs]>=8 && 'Timesheet'[Day of Week]>=5,45,

        0

        ))

I would like to have this is one measure. Thank you for your help