Forum Discussion

Coryanthony's avatar
Coryanthony
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 calculat...
Need Help
  • ppm1's avatar
    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 

Anonymous's avatar
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.