count of items basis 2 dates

[sandeep_sharma]

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

[Greg_Deckler]

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

[sandeep_sharma]

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

[Greg_Deckler]

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

[sandeep_sharma]

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

[Greg_Deckler · Accepted solution]

@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

[sandeep_sharma]

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