Power Pivot - Countif

    I have multiple spreadsheets loading in to a Power Query, then back to Excel for Power Pivots. The attached is a small sample. I want my pivot table to show as follows; Rows will show Year, Column 1 - count of individual employees each year, Column 2 a count of how many employees started that year. Column 1 is easy enough using Distinct Count. Column 2 should show 1 for 2013, 2015 and 2016, 0 for the other years. I imagine there is a Measure (or two) that can be written for this, but I can't figure it out. Any help would be greatly appreciated.


    Name Pay Date Amount Start Date
    Joe Smith 2015 1,200.00 2015
    Joe Smith 2016 1,200.00 2015
    Joe Smith 2017 1,200.00 2015
    Joe Smith 2018 1,200.00 2015
    Joe Smith 2019 1,200.00 2015
    Joe Smith 2020 1,200.00 2015
    Joe Smith 2021 1,200.00 2015
    Jane Doe 2013 1,500.00 2013
    Jane Doe 2014 1,500.00 2013
    Jane Doe 2015 1,500.00 2013
    Jane Doe 2016 1,500.00 2013
    Jane Doe 2017 1,500.00 2013
    Jane Doe 2018 1,500.00 2013
    Jane Doe 2019 1,500.00 2013
    Jane Doe 2020 1,500.00 2013
    John Deere 2016 980.00 2016
    John Deere 2017 980.00 2016
    John Deere 2018 980.00 2016
    John Deere 2019 980.00 2016
    John Deere 2020 980.00 2016
    John Deere 2021 980.00 2016

    Assuming the year you refer to as the grouping field is pay date, couldn't you just add a column to the query that returns 1 if the pay date is the same as the start date, 0 otherwise, then sum that to get a count?

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    That concept may very well work. My spreadsheet is a bit more complex though. It uses actual dates, Pay dates are on Fridays and start dates are usually on Mondays, so an exact match of dates won't work. But perhaps an if statement with less than 14 days between pay and start dates would work. Would you have any idea how to write that formula in Dax for the Power Query? Thanks

    Power Query doesn't use DAX, it uses M code.


    Can you post a more representative sample of your data and an indication of what the pivot should look like?

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    I was able to you use that logic to create a couple of custom columns in the Power Query to get to 0 and 1 and then sum the 1's in my pivot. Thanks

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login