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

[kaushikmakadia]

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.

[Greg_Deckler]

@kaushikmakadia Try:

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

[kaushikmakadia]

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

[Greg_Deckler]

@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.

[VahidDM]

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✌️!!