cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Vinit
Frequent Visitor

DAX Divide Formula

Hi,

I want DAX formula for Divide,

it likes, Measure = No. of product / (no. of days between two dates - 1 - No. of Sundays in selected period)

 

Measure =

COUNT('Production of the day'[productshortname]) / (VALUES ( 'Production of the day'[actualendtime] )- 1 - no. of sundays)

 

how should i write Correct fromula for this.

Here is context of each term, 

No. of product = COUNT('Production of the day'[productshortname]),

no. of days between two dates =  (VALUES ( 'Production of the day'[actualendtime] ), 

1 = totals selected no. of days minus 1 day (it is value not text),

No. of Sundays in selected period = no. of sundays( how should i write DAX for no. of Sundays only)

 

1 ACCEPTED SOLUTION

Hi

Just count the number of days without sundays, it is easier

 

Working Days = 
CALCULATE(
    COUNTROWS('Date Table'), 
    FILTER('Date Table', 
        Not WEEKDAY('Date Table'[Date],2) in {7}
    )
)

View solution in original post

3 REPLIES 3
JamesFr06
Super User
Super User

Hi,

 

Please give some samples of your data. Thks

Vinit_0-1665128811834.png

In the above image,

In Date, Both Date (01/09/2022) & (30/09/2022) are coming from same column

i.e. 'Production of the day'[actualendtime]

Below,     J961     SH1599     C18      BL5

              36.962    61.500     0.692    0.192

here for e.g. ,

J - Jeans(it is a product) , 961 - Total product count between 01/09/2022 & 30/09/2022, 

36.961 = 961/(30-4) i.e. 30- total selected days & 4 - no. of sundays between selected days(01/09/2022)&(30/09/2022).

 

here is contect , 

Production of the day'[productshortname] = J

COUNT('Production of the day'[productshortname]) = 961

VALUES ( 'Production of the day'[actualendtime] ) = 30

no. of sundays i.e. 01/09/2022 & 30/09/2022 = 4

( how should i write DAX for no. of Sundays only between any selected date)

 

So I required DAX formula for 36.961 i.e 961/(30-4) 

hope you get it.

Hi

Just count the number of days without sundays, it is easier

 

Working Days = 
CALCULATE(
    COUNTROWS('Date Table'), 
    FILTER('Date Table', 
        Not WEEKDAY('Date Table'[Date],2) in {7}
    )
)

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors