Count Ifs in Power Query

[msandu]

Hello,

I'm trying to calculate the business hours in a duration between a start date and an end date for each row in my table, to calculate an average value. The business hours interval is a set one (7am - 6pm) and I've created a new table that contains all the days for a couple of years and whether they are business days or vacation days (couldn't find an online free service...); looks like this:

DATE	ISVAK
01/01/2017 00:00	1
02/01/2017 00:00	0
03/01/2017 00:00	0
04/01/2017 00:00	0
05/01/2017 00:00	0

So, if it's a business day ISVAK is 0 and if vacation is 1. Additionally, I created a bunch of new columns using power query that would round the start dates and the end dates to the start or the end of the business hours interval if they are out of BH, and leave them as they are if they are in BH (start date goes to the next 7am and end date to the last 6pm if out BH, if in BH both stay the same). I put together a formula to calculate this duration for each of my records that looks like this:

BH Duration = End - Start - ((Count of business days between start and end)-1)*13hrs + (Count of vacation days between start and end) * 24hrs)

And this is where I'm stuck and asking for help. Is there a way to count the 1s for the dates that are between the start date and the end date using power query (like the Countifs in Excel – countifs(isvak, 1, date, ”>=”&start, date, ”<=”&end)? Is there a way to calculate this duration using DAX?

Thank you for your time,

Marius

[vanessafvg · Accepted solution]

something like this? create a new measure in dax

calculate(sum(isvak), filter (tablename, datetable[date] >= startdate > && datetable[date] <=enddate))

[msandu]

Ah just saw that you can create new columns using DAX, beside power query. Thank you for the formula!

[vanessafvg]

Yes you can but for aggregated data, make sure you use a calculated measure rather than column otherwise you create an unecessary load on your model

[msandu]

Is there a way to do a DAX measurement that would calculate the average of the results of all the formulas like (End - Start - ((Count of business days between start and end)-1)*13hrs + (Count of vacation days between start and end) * 24hrs)) applied to each row in my table?

[vanessafvg]

You can created measures on measures for sure..

look into using quick measures based on your skill level

https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-quick-measures/

[msandu]

Thank you Vanessa for all your assistance!