Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
chetan8080
Frequent Visitor

calculated columns to filter by max & min date

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.

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

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

1.png2.png3.png5.png6.png7.png

 

1.png2.png

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] )
)

 

View solution in original post

7 REPLIES 7
tamerj1
Super User
Super User

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

1.png2.png3.png5.png6.png7.png

 

1.png2.png

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
Frequent Visitor

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

@chetan8080 

Wold you please provide sample expected results?

chetan8080_0-1656253184076.png

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.

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

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

Thank you : )

tamerj1
Super User
Super User

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?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.