Dax Help for No of working days

[RRaj_293]

Hi , 

I am trying to create a dax for calculating the number of days between Despatch date and target despatch date. Expected results as below. The calcualtion should also take into consideration different warehouses working days and public holidays into consideration. Weekends and public holidays to be excluded. 

Below is the data model -

Date table - is a pure calendar table .

Warheouse table - Dimension table which has warheouse details.

Warehouse calendare table - This table holds the ,calendar date information for every warehouse. It has a field called "is Working Day" = Treu/False , which indicates where its a working day/not for every warehouse code for all years.

Fact table - holds all the dates and other info. Warehouse date calendar is not direclty connected to Fact table . It is connected via warehouse table

The dax(below) I wrote doesnt seem to work as the "Is working Day" filter is not getting applied although used in filter of the dax.Not sure if its b'coz there is no direct relation between fact and warehouse calendar.

Not excluding weekends-
NoofDays_line = var startdate = IF(SELECTEDVALUE('Fact'[Despatch Date]) = BLANK() , BLANK(),SELECTEDVALUE('Fact'[Despatch Date]))
var enddate = IF(SELECTEDVALUE('Fact'[Target Despatch Date])= BLANK() , BLANK(),SELECTEDVALUE('Fact'[Target Despatch Date]))
VAR WarehouseCode = SELECTEDVALUE('Fact'[Warehouse_Code])
Return
CALCULATE(DATEDIFF(startdate,enddate,DAY),
FILTER('Warehouse','Warehouse'[Warehouse Code] = WarehouseCode),FILTER(ALL('Warehouse Date Calendar'),'Warehouse Date Calendar'[Is Working Day] = TRUE()))

Expected result-

Note- Need both + and - sceanrios where despatch date could be On, before or After target despatch date.

15-Sep-23	17-Sep-23	Melb	2	16,17 hence 2 days.
21-Sep-23	18-Sep-23	Melb	-3	It shld be a negative number as despatch date is after the target depstach date.
22-Sep-23	28-Sep-23	NSW	2	Assume 27th Sep was a public holiday only in NSW hence 2 days excluding weekends and Public holidays
21-Sep-23	21-sEP-23	NSW	0	as both dates are same.
22-Sep-23	28-Sep-23	Melb	4	No public holidays , only weekends excluded.

[Anonymous]

Hi @RRaj_293 ,

Please have a try.

NoofDays_line = 
VAR StartDate = SELECTEDVALUE ( 'Fact'[Despatch Date] )
VAR EndDate = SELECTEDVALUE ( 'Fact'[Target Despatch Date] )
VAR WarehouseCode = SELECTEDVALUE ( 'Fact'[Warehouse_Code] )
RETURN
CALCULATE (
    COUNTROWS ( 'Warehouse Date Calendar' ),
    USERELATIONSHIP ( 'Fact'[Warehouse_Code], 'Warehouse Date Calendar'[Warehouse Code] ),
    'Warehouse Date Calendar'[Is Working Day] = TRUE (),
    DATESBETWEEN ( 'Warehouse Date Calendar'[Date], StartDate, EndDate )
)

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[RRaj_293]

Thank you so much for responding. Since there was no response for couple of days after posting , I resolved it in SQL after really trying hard for days .

However on the Dax (from my learning perspective), doing a count of rows for dates between start and end date does not give me the negative numbers where despatch date is greater than target despatch date. which is why I used datesdiff . so I'm pretty sure this dax wont fully serve the purpose as I did the same before arrving with Datesdiff. For numbers which have to show up as negative it will return 0 and in business terms it would mean orders were despatched on the same day as target day which is incorrect. 

Appreciate your time and effort in responding to my query. Thanks a lot.

[RRaj_293]

Sorry table missing column names - here you go.

despatch date	Target Despatch date	Warehouse	No.Of working days
15-Sep-23	17-Sep-23	Melb	2	16,17 hence 2 days.
21-Sep-23	18-Sep-23	Melb	-3	It shld be a negative number as despatch date is after the target depstach date.
22-Sep-23	28-Sep-23	NSW	2	Assume 27th Sep was a public holiday only in NSW hence 2 days excluding weekends and Public holidays
22-Sep-23	28-Sep-23	Melb	4	No public holidays , only weekends excluded.