Forum Discussion

AMR80's avatar
AMR80
Frequent Visitor
5 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 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? 

Need Help
  • v-janeyg-msft's avatar
    v-janeyg-msft
    5 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.

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

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

    • AMR80's avatar
      AMR80
      Frequent Visitor
      5 years ago

      Hi Anonymous 

       

      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. 

       

      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"))) )

  • v-janeyg-msft's avatar
    v-janeyg-msft
    Community Support
    5 years ago

    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.

    • AMR80's avatar
      AMR80
      Frequent Visitor
      5 years ago

      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: 

      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: 

      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 

       

       

      • v-janeyg-msft's avatar
        v-janeyg-msft
        Community Support
        5 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.