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.

[CNENFRNL · Accepted solution]

Measure = 
CALCULATE(
    DISTINCTCOUNT( SALES[Sales_Date] ),
    FILTER(
        SUMMARIZE( SALES, SALES[Sales_Date], SALES[Sales_Item] ),
        CALCULATE(
            NOT ISEMPTY( SALES ),
            SALES[Sales_Status] <> "Closed"
                || SALES[Sales_Status] = "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.

[Greg_Deckler]

@kaushikmakadia Try:

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