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 

ppm1's avatar
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