Need Help evaluating number of days with a given slicer (w/ vacation)

Imrans23 — Thu, 28 May 2020 09:08:26 GMT

Hi,

I have a BI report where I would like to compare production data with a daily target. I have a production colun which shows the production for each day. ALong with that, I have a datediff function which works very well with my slicer where

StartDate = CALCULATE ( MIN ( [Production Date ] ), ALLSELECTED ( [Production Date ] ) )

EndDate = CALCULATE ( MAX ( [Production Date ] ), ALLSELECTED ( Production Date ] ) )

DateDifff = CALCULATE (
DATEDIFF ([StartDate],[EndDate],DAY)
)

Then I have

Measure 4 = Value([DateDifff]) * x

Here, x will be a number representing my Daily Target.

The issue I am facing is that I don't do production everyday. In otherwords, on weekends and vacation days. My daily target should be 0. So I was thinking of doing something like

Measure 4 = (Value([DateDifff])-[Vacation_days]) * x

I am having trouble finding out how I can evaluate vacation days. Can I have another table which lists down every date? Or is there a way I can mention every Saturday to be an off day?

Any help would be highly appreciated

Re: Need Help evaluating number of days with a given slicer (w/ vacation)

HotChilli — Fri, 29 May 2020 01:35:33 GMT

Yes, you need a date table and you need a column within that designates off days. (or IsWorkingDay column)

Links here :

https://www.red-gate.com/simple-talk/sql/bi/using-calendars-and-dates-in-power-bi/

https://www.andredevelopment.com/en-US/community/blogs/powerbi/working-days-holidays-calendar/

topic Re: Need Help evaluating number of days with a given slicer (w/ vacation) in DAX Commands and Tips

Need Help evaluating number of days with a given slicer (w/ vacation)

Re: Need Help evaluating number of days with a given slicer (w/ vacation)