Helper III

3 years ago

Solved

Calculation with Time Intelligence

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,

))

Need Help

ppm1

3 years ago

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

5 Replies

ppm1
Solution Sage
3 years ago
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
  Helper III
  3 years ago
  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
Ashish_Mathur
Super User
3 years ago
Hi,

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

Anonymous

Not applicable

3 years ago

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.

ppm1

Solution Sage

3 years ago

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

Forum Discussion

Calculation with Time Intelligence

5 Replies

Related content

Interesting effects with calendar-based time intelligence

Issue with SAMEPERIODLASTYEAR and Manual Time Intelligence

MoM calculation

Time Intelligence Calculations

Getting error from time intelligence calculation group