Forum Discussion

AMR80's avatar
AMR80
Frequent Visitor
6 years ago
Solved

SUMX with Filter or If Statement

I have custom columns to calculate the balance of an Employee's time off by subtracting their Time Used so far in the year from their Time Allotted.  All the types of time are straight forward excep...
Need Help
  • v-janeyg-msft's avatar
    v-janeyg-msft
    6 years ago

    Hi, @AMR80

    Sorry for my late response.

    It's not recommended that you calculate the aggregate value in Power Query. Power Query uses M language to write formulas,and using custom column will change the original data model.

    v-janeyg-msft_0-1600917878535.png

    You can create a measure in the model. The measure is mainly used for aggregation calculation, and the calculations are executed during query.

    Like this:

    Measure = SUMX (
    FILTER (
        'Table_query__5',
        AND (
            [Date]
                > TODAY () - 365
                && [Date] <= TODAY (),
                 [Type of Time] = "Family Leave"
        )
    ),
    [Hours Requested] 
)

    Snipaste_2020-09-24_11-32-20.png

    v-janeyg-msft_2-1600917878540.png

    If you still have questions, please feel free to contact me.

    Best Regards

    Janey Guo

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

AMR80's avatar
AMR80
Frequent Visitor
6 years ago

This is great v-janeyg-msft. The measure worked perfectly to calculate the FMLA time used 🙂 Now my challenge is to take this measure to the next level. What would be really great is if I could get a Measure that calculated this: 

If type of time is "family leave", do this: 

SUMX (
FILTER (
'Employee Time Used - Hours',
AND (
[Date]
> TODAY () - 365
&& [Date] <= TODAY (),
),
[Hours Requested]
)

If type of time is "personal hours", do this: 

SUMX (
FILTER (
'Employee Time Used - Hours',
AND (
[Date]
> [Personal Hours Reset Date] - 365
&& [Date] <= TODAY (),
),
[Hours Requested]
)

For all other types of time, do this: Sum of all hours used in the current year. 

Calc Hrs Used = SUMX (
FILTER (
'Employee Time Used - Hours',
[Year] = DATE (CurrentYear, 01, 01)
),
[Hours Requested]
)
 
I am not sure how to combine the three in a measure. Is it possible? Something like this?:
 
SUMX(IF([Type of Time] = "Family Leave", FILTER('Employee Time Used - Hours', AND([Date] > TODAY - 365 && [Date] <= TODAY(),),[Hours Requested]),IF([Type of Time] = "Personal Hours", FILTER('Employee Time Used - Hours', AND([Date] > [Personal Hours Reset Date] - 365 && [Date] <= TODAY(),),[Hours Requested]),FILTER('Employee Time Used - Hours', [Year] = [Date] (CurrentYear, 01, 01)),[Hours Requested]))
v-janeyg-msft's avatar
v-janeyg-msft
Icon for Community Support rankCommunity Support
6 years ago

Hi, AMR80 

 

According to your description, I think you can use the switch function to combine these three formulas in one measure.

Like this:

 

 

Measure =
SWITCH (
    SELECTEDVALUE ( Table_query__5[Type of Time] ),
    "Family Leave",
        SUMX (
            FILTER ( Table_query__5, [Date] > TODAY () - 365 && [Date] <= TODAY () ),
            [Hours Requested]
        ),
    "personal hours",
        SUMX (
            FILTER (
                Table_query__5,
                [Date] > [Personal Hours Reset Date] - 365
                    && [Date] <= TODAY ()
            ),
            [Hours Requested]
        ),
    SUMX (
        FILTER ( Table_query__5, YEAR ( [Date] ) = YEAR ( TODAY () ) ),
        [Hours Requested]
    )
)

 

 

Due to the complexity of the measure, total cann't get the correct result in the context, you can refer to this article:

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376 

 

IF you have other questions,please feel free to contact me.

Best Regards

Janey Guo

 

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