Excel formula to Dax

[Anonymous]

Hi,

Could somebody help me with translating my two excel formulas in dax?

I've already calculated my workingdaysflag column which already has bank holidays incorporated.

COUNTIF (BankHolidays,[End Date])>0)),"0",

NETWORKDAYS(ParameterStartDate,[End Date],BankHolidays)-1)

Thanks

[amitchandak]

@Anonymous , refer to this file on 2nd page how working to get working day

https://www.dropbox.com/s/y47ah38sr157l7t/Order_delivery_date_diff.pbix?dl=0

[Anonymous]

@amitchandak ,

This file wont open. It says Object reference not set to an instance of an object

[dax]

Hi @Anonymous , 

I am not clear about your requirement, if possible could you please inform me more detailed information(such as your expected output and your sample data)? Then I will help you more correctly.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

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

[Anonymous]

Hi @dax ,

It was counting how many dates in the 'Start Date' column existed in the BankHoliday date column in the Bankholiday table.

These tables arent related. I tried something like below but it wont work

CALCULATE(COUNT(vwRB_RoomUtilisation[New ]), FILTER(BankHols, BankHols[Date] >0))

Thanks

[dax · Accepted solution]

Hi @Anonymous , 

I am not clear  about your requirement, you could refer to my sample for details. If this is not waht you want, please correct me and inform me more detailed information(such as your expected output and your sample data )? Then I will help you more correctly.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

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

[AntrikshSharma]

COUNTX(
    BankHolidays, 
    BankHolidays[End Date] > 0
)

For NETWORKDAYS here is solved post. https://community.powerbi.com/t5/Desktop/equivalent-function-of-Networkdays-in-DAX/td-p/245103

[Anonymous]

hi @AntrikshSharma 

Thanks for that. Regarding the 

COUNTX(
    BankHolidays, 
    BankHolidays[End Date] > 0
)

My bank holiday column just has a name for bank holiday description and if its not then Its blank. Would this be done in a different way to count instead of countx?

. 

[AntrikshSharma]

You could use 

COUNTX (
    BankHolidays,
    NOT ISBLANK ( BankHolidays[End Date] )
)

[Anonymous]

hi @AntrikshSharma ,

Apologies, I still need it to be similar to COUNTIF (BankHolidays,[End Date])>0) with the greater than 0. 

In Power BI the End Date that refers to in the excel formula above is in table 2 and bank holiday day desc is table 1.

Thanks