calculated columns to filter by max & min date

[chetan8080]

Hello Community!
I Have date field in a slicer in which 2 dates are selected ( Max& Min)
I have a problem where I need to have 2 columns of products in a table, one should give me details of products of Max date & other column should give details of products of Min date in the same table.

Thank you in Advance.

[tamerj1 · Accepted solution]

Hi @chetan8080 
Here is a samp[le file with the solution https://we.tl/t-SeyYXbCoV0

Filter Table = 
VAR NumOfProducts = COUNTROWS ( DISTINCT ( Data[Product] ) )
VAR ProductIndexTable = GENERATESERIES ( 1, NumOfProducts, 1 )
VAR AllDatesTable = CALENDAR ( MIN ( Data[Date] ), MAX ( Data[Date] ) )
VAR DatesWithIndex = CROSSJOIN ( ProductIndexTable, AllDatesTable )
RETURN
    DatesWithIndex

Index = RANKX ( Data, Data[Product],, ASC, Dense )

Date Measure = 
MAX ( 'Filter Table'[Date] )

Product Measure = 
CALCULATE ( 
    MAX ( Data[Product] ),
    Data[Date] = MAX ('Filter Table'[Date] )
)

[chetan8080]

Hi @tamerj1 , thank you for coming up.
All the fields like date, products are from same table.

[tamerj1]

@chetan8080 

Wold you please provide sample expected results?

[chetan8080]

I have provided how the power bi table should look like, The slicer is multi-selected with 2 dates: 1/1/2022 & 2/1/2022.
The products details for 1/1/2022 should be in first column & product details of 2/1/2022 in a second column of the same table.

[tamerj1]

@chetan8080 

I was sbout to say that this cannot be achieved but I gave it another thought and I believe it can be achieved if we have an index column. I will try to produce something by tomorrow morning and get back to you. 

[chetan8080]

sure @tamerj1 , that will help a lot!!. I will be waiting for your reply.

Thank you : )

[tamerj1]

Hi @chetan8080 

this date field in the slicer from which table? If the same table then which column? Of another table do you have a relationship between this table and the fact table? Based on which column?