Total monthly workdays and last business day

[Anonymous]

I currently have an excel dashboard which I use "Last Business Day Sales".  We work Monday through Friday, off weekends. This  formula will give last Friday's sales if it is Monday and it will take holidays into consideration.  How do I convert this to DAX for my Power BI reports?

=SUMIFS(HLn_Total,Ship_Date,WORKDAY(TODAY(),-1,holiday),OurRef,"<>2015*")

I also have a formula which calculates the total number of business days in a month, while calculating how many business days has been completed.  How do I convert this to DAX for my Power BI reports?

=NETWORKDAYS(EOMONTH(TODAY(),-1)+1,TODAY()-1,holiday)&" of "&NETWORKDAYS(TODAY()-DAY(TODAY())+1,DATE(YEAR(TODAY()),MONTH(TODAY())+1,0),holiday)&" Business Days Completed"

If someone could please advice or point me to a place with the answer.  Thank you.

BTW, I'm new to Power BI and it is amazing!!

[v-caliao-msft]

Hi Usates,

To calculate the working day in each month, you need to have a calendar table in your model. Besides, you said that it will take holidays into consideration, so you need a table containing the holidays, with a description of the non working reason. Here is a blog which describes how to calculate working day in PowerPivot data model by using DAX, please refer to the link below.
http://sqlblog.com/blogs/alberto_ferrari/archive/2011/01/19/working-days-computation-in-powerpivot.a...

Regards,

Charlie Liao

[Greg_Deckler]

Would be great to have some sample data.

In general, to solve this (the first one) would be something along the lines of creating a measure like:

CALCULATE(SUM([SomeColumn]),FILTERS...)

The FILTERS... is pseudocode. Basically, to replace SUMIFS, you would wrap a SUM in a CALCULATE and apply filters to it like [Date] > TODAY()-1 or something like that. 

For the second one, you could use WEEKDAY in a new column formula to return the day of the week like:

DayOfWeek = WEEKDAY([Date],2)

Then, you could also create a new column like:

Month = MONTH([Date])

You could then create a measure like:

WorkDays = CALCULATE(COUNT([SomeColumn]),DayOfWeek < 6)

Then you put Month and WorkDays in a table.

Overall, tough to help out more without some data.

[Anonymous]

The first formula pulls the last business days information.  If I put [Date] today()-1, on Monday, it will give me Sunday sales, which we are not open on Sunday so I'm trying to avoid that. I want to see Friday's sales on Monday.  Also, if a holiday, like July 4th, is on a Wednesday, Thursday morning I want to see Tuesday's sales.

The second solution you posted does not take holidays into consideration.  Is there a Dax option that will subtract out holidays?

Basically, I want a dax formula that will tell me how many workday are in a given month, minus holidays and weekends.

[Greg_Deckler]

What you need is a date table where you can flag your non-working days and/or holidays. I would recommend starting with something like DateStream:

http://datamarket.azure.com/dataset/boyanpenev/datestream

Once you have your date table in place, you can refactor the formulas to account for non-working days and holidays.

Alternatively, you could probably put your holidays in a table by themselves and we could see where we go from there.