Forum Discussion
Helper IIcount of items basis 2 dates
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......
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
6 Replies
- Greg_Deckler
Community Champion
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 __ResultOtherwise, please provide sample data and expected results.
sandeep_sharmaNot 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.