topic Re: DAX Count Number of Days in a given month from a date dimension where other table condition meet in DAX Commands and Tips

DAX Count Number of Days in a given month from a date dimension where other table condition meet

kaushikmakadia — Wed, 08 Sep 2021 00:05:50 GMT

Hi there

I need help with DAX.

Have below two tables :

Calendar_Dimension_Table

The_Date Year Month

2020-08-15 2020 August

2020-08-16 2020 August

2020-08-17 2020 August

.............

Sales_Table

Sales_Date Sales_Item Sales_Status Sales_Category

2020-08-15 IT123 OPEN MAJOR

2020-08-15 ITYYY OPEN MAJOR

2020-08-15 IT876 CLOSED MINOR

2020-08-15 IT876 SUBMIT MINOR

2020-08-15 IT876 PROCESS MINOR

2020-08-16 IT123 CLOSED MAJOR

2020-08-17 ITABC OPEN MAJOR

2020-08-17 ITXYZ OPEN MAJOR

.............

Tables are join with The_Date and Sales_Date columns.

I am after DAX that provide number of days per calender month where sales status is not closed or sales category is minor.

Expected output is 2 days for Auguest (because 15th Aug and 17th Aug)

Many Thanks.

Re: DAX Count Number of Days in a given month from a date dimension where other table condition meet

Greg_Deckler — Wed, 08 Sep 2021 00:16:49 GMT

kaushikmakadia Try:

Measure = COUNTROWS(FILTER('Sales_Table',[Sales_Status]="OPEN" && [Sales_Category]="MINOR"))

Re: DAX Count Number of Days in a given month from a date dimension where other table condition meet

kaushikmakadia — Wed, 08 Sep 2021 00:51:53 GMT

Not really. COUNTROWS does not give distinct number of days in a month where condition meet (per my original post requirement). Any idea pls

Re: DAX Count Number of Days in a given month from a date dimension where other table condition meet

Greg_Deckler — Wed, 08 Sep 2021 02:05:13 GMT

kaushikmakadia So then this:

Measure = COUNTROWS( DISTINCT( SELECTCOLUMNS( FILTER('Sales_Table',[Sales_Status]="OPEN" && [Sales_Category]="MINOR") "Date",[Sales_Date] ) ) )

Try not to cross-post please.

Re: DAX Count Number of Days in a given month from a date dimension where other table condition meet

VahidDM — Wed, 08 Sep 2021 03:16:44 GMT

Hi kaushikmakadia

Try this measure:

number of days = CALCULATE ( DISTINCTCOUNT ( Sales_Table[Sales_Date ] ), FILTER ( Sales_Table, Sales_Table[ Sales_Status ] <> "CLOSED" || Sales_Table[ Sales_Category] = "MINOR" ) )

Output:

Download file: https://gofile.io/d/nTX5fZ

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

Appreciate your Kudos✌️!!