Dax Sales by Bus Day

[techies]

Hi @jj1 please check this

[jj1]

ok

[v-dineshya · Accepted solution]

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

[v-dineshya]

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

[v-dineshya]

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

[v-dineshya]

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.

[Anonymous]

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

[techies]

Hi @jj1 hope this is as required

Total Sales Business Days =

CALCULATE(

    SUM('Sales'[Sales]),

    'date'[Week Value] <= 5

)

[jj1]

ok

[techies]

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)

[jj1]

ok

[techies]

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)

[jj1]

same error even with that new approach you recommended

[jj1]

ok

[jj1]

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

[jj1]

Defining

[techies]

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)

[jj1]

Thanks Let me try this-

[techies]

ok