cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

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 except for FMLA time which works on a rolling calendar year. It can't look at all of the 2020 data, it has to look at data from today and back 365 days; so some of 2019 data too.  

Is there anyway to use a SUMX, Filter or some sort of formula in a custom column to sum the amount of hours used for each employee from today and back 365 days? It will need to dynamically change each day to ultimately give me their balance, becuase I will subtract the Time Used number generated by this custom measure from the Allotted time given to each employee? 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

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.

View solution in original post

7 REPLIES 7
Highlighted
Resolver I
Resolver I

Hi Maybe some sample data and expected result will help to resolve this soon. Thanks!

Highlighted

Hi @alex00321 

 

Here is what I have right now, but I don't like using the date slider to filter the chart becuase FMLA is the only type of time that gets calculated on a rolling calendar year so I'd rather calculate it in the query. 

one.jpg

 

I was thinking something like one of the formulas below would suffice as a custom column or measure? Not sure. I don't have too much experience with either. Also, my source is a SharePoint online list, not a table. I don't know if these formulas will work with that type of data source. 

 

FMLA Measure = SUMX(FILTER(ALL(tablename),AND([Date]>Today()-365,[Date]<=Today(),[Type of Time] = "Family Leave"))),[Hours Requested])

 

FMLA Measure = CALCULATE(SUM([Qty]),FILTER(ALL(tablename),AND([Date]>Today()-365,[Date]<=Today(),[Type of Time] = "Family Leave"))) )

Highlighted
Microsoft
Microsoft

Hi, @AMR80 

 

According to your description, I think you can modify the measure.Like this:

FMLA Measure =
SUMX (
    FILTER (
        ALL ( tablename ),
        AND (
            [Date]
                > TODAY () - 365
                && [Date] <= TODAY (),
            [Type of Time] = "Family Leave"
        )
    ),
    [Hours Requested]
)
FMLA Measure =
CALCULATE (
    SUM ( [Qty] ),
    FILTER (
        ALL ( tablename ),
        AND (
            [Date]
                > TODAY () - 365
                && [Date] <= TODAY (),
            [Type of Time] = "Family Leave"
        )
    )
)

If it doesn’t meet your requirements, please share some fake data with onedrive for business or pictures.

Do mask your sensitive data before uploading.

 

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.

Highlighted

Thanks @v-janeyg-msft 

I tried this: 

= Table.AddColumn(#"Filtered Rows", "Custom", each SUMX(FILTER(ALL(#"Employee Time Used - FMLA" ),AND ([Date] > TODAY () - 365 & [Date] <= TODAY (),[Type of Time] = "Family Leave")),[Hours Requested]))

And got an error: 

AMR80_0-1600888581263.png

Also tried this: 
= Table.AddColumn(#"Filtered Rows", "Custom", each CALCULATE (SUM ( [Hours Requested] ),FILTER (ALL ( #"Employee Time Used - FMLA" ),AND ([Date] > TODAY () - 365 & [Date] <= TODAY (), [Type of Time] = "Family Leave"))))

And got a differnet error: 

AMR80_1-1600889398272.png

I've attached an Excel doc with dummy data. I need to find the SUM of the HOURS REQUESTED for EMPLOYEE John Doe only for the TYPE of TIME "Family Leave". I already have a filter step for Type of Time in PBI, so there is no real need to include that expression/condition in the formula. What is important is the Date. We only want to SUM the HOURS REQUESTED between Today()-365 and Today for each employee. The answer for John Doe would be 200 hours were REQUESTED (ie. Used during the current rolling calendar year). The answer will change on Oct 15th though based on the dates in the dummy data. 
FILE LINK 

AMR80_3-1600890418263.png

 

 

Highlighted

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.

View solution in original post

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]))
Highlighted

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.

 

 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors