Counts in Specific conditions

[kamalmalek20]

dear All, i need Dax Formula to add new column in table with following condition

number of days in same month included item in store, example : item 1111 in Store A repeated in 1-2-3 Oct so repeated days is 3, "Note i need in same month " so if you have data in 

Date Store item Repeated days in this month

Date            str     item     Days repeated per month

10/1/2024    A    1111      3
10/1/2024    B    1111      1
10/1/2024   C     2222     1
10/1/2024   D    3333      2
10/1/2024   A    1111      3
10/2/2024   A    1111      3
10/3/2024   A    1111      3
10/3/2024   A    1111      3
10/3/2024   D    3333     2

[kamalmalek20]

Dear Supporters , Can you please support this issue , i am seeking your proposed solution  

[v-xinruzhu-msft]

Hi,

Thanks for the solution @Greg_Deckler  offered, and i want to offer some more information for user to refer to.

hello @kamalmalek20 , you can create the following calculated column.

Column =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Date] ),
    FILTER (
        'Table',
        'Table'[str] = EARLIER ( 'Table'[str] )
            && [item] = EARLIER ( 'Table'[item] )
            && EOMONTH ( [Date], 0 ) = EOMONTH ( EARLIER ( 'Table'[Date] ), 0 )
    )
)

Output

Best Regards!

Yolo Zhu

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

[kamalmalek20]

thanks a lot , but this formula taking much time of loading then taking much time to refresh data 

[v-xinruzhu-msft]

Hi @kamalmalek20 

Please try this

Column =
VAR a = 'Table'[str]
VAR b = 'Table'[item]
VAR c = [Date]
VAR _table =
    FILTER (
        'Table',
        'Table'[str] = a
            && [item] = b
            && EOMONTH ( [Date], 0 ) = EOMONTH ( c, 0 )
    )
RETURN
    CALCULATE ( DISTINCTCOUNT ( 'Table'[Date] ), _table )

Best Regards!

Yolo Zhu

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

[kamalmalek20]

thanks for supporting , 30 min and still runing 😞

[Greg_Deckler]

@kamalmalek20 Try:

Column = 
    VAR __Item = [Item]
    VAR __MonthYear = YEAR( 'Table'[Date] ) * 100 + MONTH( 'Table'[Date] )
    VAR __Table = ADDCOLUMNS( 'Table', "__MonthYear", YEAR( [Date] ) * 100 + MONTH( [Date] ) )
    VAR __Table1 = SUMMARIZE( FILTER( __Table, [__MonthYear] = [__MonthYear] && [Item] = __Item ), [Item], [Date] )
    VAR __Result = COUNTROWS( __Table1 )
RETURN
    __Result

[kamalmalek20]

dear , its not working then you didnt mention store becuase it is related to date and store and item