topic Re: Dax Sales by Bus Day in DAX Commands and Tips

Dax Sales by Bus Day

jj1 — Wed, 16 Apr 2025 01:01:31 GMT

Re: Dax Sales by Bus Day

techies — Fri, 28 Mar 2025 10:57:17 GMT

Hi jj1 hope this is as required

Total Sales Business Days =

CALCULATE(

SUM('Sales'[Sales]),

'date'[Week Value] <= 5

)

Re: Dax Sales by Bus Day

Anonymous — Fri, 28 Mar 2025 12:52:55 GMT

Hi jj1,

Thank you for reaching out to Microsoft Fabric Community Forum.

Please find below DAX code.

SalesByWorkigDays = CALCULATE( SUM('Revenue table'[Sales]), 'Calc Table'[ISWORKINGDay] IN {1, 2, 3, 4, 5} )

I took sample dataset, please refer the snap

Please find the result set based on sample dataset

As you mentioned, Table 1 (Calc Table) and Table 2 (Revenue Table) are the two data sources. Based on my case, there is no relationship between the two tables, which led to unexpected results.

If you have valid relationship between tables the results will be as expected

Thanks

Vinay Pabbu

Re: Dax Sales by Bus Day

jj1 — Wed, 16 Apr 2025 01:02:33 GMT

Re: Dax Sales by Bus Day

techies — Fri, 28 Mar 2025 17:47:50 GMT

Hi jj1 create calculated column in date table as this

IsBusinessDay =

VAR Weekend = WEEKDAY('Date'[Date], 2) >= 6

RETURN

IF(Weekend, FALSE(), TRUE())

and then create measure avg business day

Avg business days = VAR BusinessDayCount =

CALCULATE(

DISTINCTCOUNT('date'[Date]),

'date'[IsBusinessDay] = TRUE()

)

VAR TotalSalesBusinessDays =

CALCULATE(

SUM('Sales'[Sales]),

'date'[IsBusinessDay] = TRUE()

)

RETURN

DIVIDE(TotalSalesBusinessDays, BusinessDayCount, 0)

Re: Dax Sales by Bus Day

jj1 — Mon, 31 Mar 2025 15:46:09 GMT

Thanks Let me try this-

Re: Dax Sales by Bus Day

techies — Mon, 31 Mar 2025 15:58:30 GMT

Re: Dax Sales by Bus Day

jj1 — Wed, 16 Apr 2025 01:03:39 GMT

Defining

Re: Dax Sales by Bus Day

techies — Mon, 31 Mar 2025 16:17:44 GMT

yes, this is the one measure for the average business day

Avg business days =

VAR BusinessDayCount =

CALCULATE(

DISTINCTCOUNT('date'[Date]),

'date'[IsBusinessDay] = TRUE()

)

VAR TotalSalesBusinessDays =

CALCULATE(

SUM('Sales'[Sales]),

'date'[IsBusinessDay] = TRUE()

)

RETURN

DIVIDE(TotalSalesBusinessDays, BusinessDayCount, 0)

Re: Dax Sales by Bus Day

jj1 — Mon, 31 Mar 2025 16:47:30 GMT

measure provided is fine but now an error with BI Dax visual

Re: Dax Sales by Bus Day

jj1 — Wed, 16 Apr 2025 01:03:11 GMT

Re: Dax Sales by Bus Day

techies — Mon, 31 Mar 2025 17:02:33 GMT

try adding like this --

Revenue Bus Day =
VAR BusinessDayCount =
CALCULATE(
DISTINCTCOUNT('Date Table'[date]),
'Date Table'[Bus Day] = TRUE()
)

VAR TotalSales =
SUM(JJProduct[Grand_Total])
- SUM(JJProduct[Discount_Total])
- SUM(JJProduct[Sales_Tax])

VAR TotalSalesBusinessDays =
CALCULATE(
TotalSales,
'Date Table'[Bus Day] = TRUE()
)

RETURN
DIVIDE(TotalSalesBusinessDays, BusinessDayCount, 0)

Re: Dax Sales by Bus Day

jj1 — Wed, 16 Apr 2025 01:04:05 GMT

Re: Dax Sales by Bus Day

jj1 — Mon, 31 Mar 2025 21:58:19 GMT

same error even with that new approach you recommended

Re: Dax Sales by Bus Day

techies — Tue, 01 Apr 2025 04:16:35 GMT

Hi jj1 please check this

Re: Dax Sales by Bus Day

jj1 — Wed, 16 Apr 2025 01:02:04 GMT

Re: Dax Sales by Bus Day

v-dineshya — Wed, 09 Apr 2025 12:45:19 GMT

Hi jj1 ,

Thanks for reaching out to the Microsoft fabric community forum.

Could you please share PBIX file with sample data?, it must be data type error or data modeling error .This will help us better understand your issue and guide you toward a solution.

If you find this post helpful, please mark it as an "Accept as Solution" and consider giving a KUDOS. Feel free to reach out if you need further assistance.
Thanks and Regards

Re: Dax Sales by Bus Day

v-dineshya — Tue, 15 Apr 2025 06:49:07 GMT

Hi jj1 ,

Thanks for reaching out to the Microsoft fabric community forum.

Could you please share PBIX file with sample data?, it must be data type error or data modeling error .This will help us better understand your issue and guide you toward a solution.

If you find this post helpful, please mark it as an "Accept as Solution" and consider giving a KUDOS. Feel free to reach out if you need further assistance.
Thanks and Regards

Re: Dax Sales by Bus Day

v-dineshya — Tue, 22 Apr 2025 04:30:00 GMT

Hi jj1 ,

Thanks for reaching out to the Microsoft fabric community forum.

Could you please share PBIX file with sample data?, it must be data type error or data modeling error .This will help us better understand your issue and guide you toward a solution.

If you find this post helpful, please mark it as an "Accept as Solution" and consider giving a KUDOS. Feel free to reach out if you need further assistance.
Thanks and Regards

Re: Dax Sales by Bus Day

v-dineshya — Fri, 06 Jun 2025 16:07:10 GMT

Hi jj1 ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet.In case we do not receive a response, we shall proceed to close this thread. For any further discussions or queries, please initiate a new thread in the Microsoft Fabric Community Forum, where we will be glad to assist you.

Thank you.