topic Re: count of items basis 2 dates in DAX Commands and Tips

count of items basis 2 dates

sandeep_sharma — Sat, 18 May 2024 09:51:08 GMT

I have 2 tables.....one is Calendar table and another is maindatabase.......in maindatabase table.....I have 3 columns.......

1. Items

2. Received date

3. Expiration date

Received date has primary relationship with Date column of calendar table.......Expiration date has secondary relationship with date column of Calendar table.......Now I want to count the distinct items.....basis the months and want to know they are expiring in which month.....so for an example......the woids that I received in the month of Jan are expiring in which months of year....similarly the woids that I received in the month of Feb are expiring in which month of year.....can anyone please help......

Re: count of items basis 2 dates

Greg_Deckler — Sat, 18 May 2024 11:14:52 GMT

sandeep_sharma You can use USERELATIONSHIP coupled with CALCULATE or CALCULATETABLE such as:

Measure = VAR __Table = CALCULATETABLE( 'maindatabase', USERELATIONSHIP( 'Calendar'[Date], 'maindatabase'[Expiration date] ) VAR __Products = DISTINCT( SELECTCOLUMNS( __Table, "__ProductID", [ProductID] ) ) VAR __Result = COUNTROWS( __Products ) RETURN __Result

Otherwise, please provide sample data and expected results.

Re: count of items basis 2 dates

sandeep_sharma — Sat, 18 May 2024 13:24:33 GMT

Not sure if I can insert a file here....hence sharing the Snapshot of sample data......

Also, below is what I need,

Please be noted that I have a calendar table that has primary relationship with Received date column and secondary relationship with expiry date.......not sure what is the best way to show.....when I dont use calendar table and put received date in rows and expiry date in column....they dont show the hierarchy even when they have right data format.......

Re: count of items basis 2 dates

Greg_Deckler — Sat, 18 May 2024 14:27:25 GMT

sandeep_sharma Any chance you can post that sample data as text so I can copy and paste? I don't want to type all that.

Re: count of items basis 2 dates

sandeep_sharma — Sat, 18 May 2024 14:48:37 GMT

Item	Received date	Expiration Date
2636683	3/4/2024	3/4/2024
3792176	5/6/2024	6/5/2024
2054262	2/4/2024	3/5/2024
1761226	2/5/2024	3/6/2024
4884335	2/4/2024	4/4/2024
2318785	3/7/2024	4/6/2024
4548830	3/8/2024	4/7/2024
2607540	3/4/2024	4/3/2024
3100589	3/4/2024	4/3/2024
1570941	5/4/2024	5/4/2024
1228598	5/6/2024	6/5/2024
4356847	5/7/2024	6/6/2024
4855698	3/4/2024	4/3/2024
1895040	2/7/2024	3/8/2024
3904926	2/8/2024	2/8/2024
1252931	2/8/2024	2/8/2024
4007366	2/10/2024	3/31/2024
4610045	5/7/2024	6/6/2024
3020933	5/7/2024	6/6/2024
2150199	3/4/2024	4/3/2024

Re: count of items basis 2 dates

Greg_Deckler — Sat, 18 May 2024 23:50:15 GMT

sandeep_sharma There may be other methods, but I solved this using 2 calendar tables and the following measure. PBIX is attached below signature.

Measure = VAR __Received = MAX('Calendar'[MonthSort]) VAR __Expired = MAX('Expiration Calendar'[MonthSort]) VAR __Table = FILTER(ALL('Table'), MONTH( 'Table'[Received date] ) = __Received && MONTH( 'Table'[Expiration Date] ) = __Expired ) VAR __Products = DISTINCT( SELECTCOLUMNS( __Table, "__Item", [Item] ) ) VAR __Result = COUNTROWS( __Products ) RETURN __Result

Re: count of items basis 2 dates

sandeep_sharma — Sun, 19 May 2024 13:09:34 GMT

thanks Greg_Deckler . This is very innovative solution. I saw this first time and got to learn a new thing.