Forum Discussion
Counts in Specific conditions
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
7 Replies
Anonymous 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 __ResultHi,
Thanks for the solution Greg_Deckler offered, and i want to offer some more information for user to refer to.
hello Anonymous , 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.
Hi Anonymous
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.
