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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
nikhil425
Helper I
Helper I

Group by all columns and filter on Max Date

Hi All, 

I am using Live connection to a Tabular model. I have the following dataset, I should filter on records and display on the max date rows for each category. I am using filter/slicer on Date and should select 3/31/2018. However reports need to check the data from last 3 months and display only the records with max date. Please help!

 

Please find the dataset below:

CategoryDateCompanyCurrent ValueCurrent CostSectorIndustry GroupHolding Co DomicileGeography
Crude13/31/2018Crude Limited58938.833293682.2520 -Industrials2010 - Capital Goods Italy
Crude112/31/2017Crude Limited60086.893293682.2520 -Industrials2010 - Capital GoodsN/AItaly
BellsA3/31/2018Bell Limited1421132.851333585.9140- Financials4020 - Diversified Financials India
BellsA12/31/2017Bell Limited1592073.461337505.9440- Financials4020 - Diversified FinancialsN/AIndia
Colsz3/31/2018Col Limited6313251.392536439.5340- Financials4020 - Diversified Financials German
Colsz12/31/2017Col Limited6321785.582496110.2540- Financials4020 - Diversified FinancialsN/AGerman
MindsF3/31/2018Minds Limited2972144.322331269.0140- Financials4020 - Diversified Financials France
MindsF12/31/2017Minds Limited3413565.812887832.6840- Financials4020 - Diversified FinancialsN/AFrance
TetraS12/31/2017Tetrs Power8045424.484270681.0840- Financials4020 - Diversified FinancialsN/AAustria
BlissN3/31/2018Bliss Limited6212173.438678780.8940- Financials4020 - Diversified Financials UK
BlissN12/31/2017Bliss Limited6296859.418508269.940- Financials4020 - Diversified FinancialsN/AUK

 

Desired Output. 

CategoryDateCompanyCurrent ValueCurrent CostSectorIndustry GroupHolding Co DomicileGeography
Crude13/31/2018Crude Limited58938.833293682.2520 -Industrials2010 - Capital Goods Italy
BellsA3/31/2018Bell Limited1421132.851333585.9140- Financials4020 - Diversified Financials India
Colsz3/31/2018Col Limited6313251.392536439.5340- Financials4020 - Diversified Financials German
MindsF3/31/2018Minds Limited2972144.322331269.0140- Financials4020 - Diversified Financials France
TetraS12/31/2017Tetrs Power8045424.484270681.0840- Financials4020 - Diversified FinancialsN/AAustria
BlissN3/31/2018Bliss Limited6212173.438678780.8940- Financials4020 - Diversified Financials UK

 

2 ACCEPTED SOLUTIONS
v-frfei-msft
Community Support
Community Support

Hi @nikhil425,

 

You need to insert a date table into your data source firstly. Then you can use the date in the table to filter the table by creating a measure as below.

 

Measure = var sele = SELECTEDVALUE('Table'[Date])
var maxdate = CALCULATE(MAX(Table1[Date]),ALLEXCEPT(Table1,Table1[Category]))
var pre = DATEADD('Table'[Date],-3,MONTH)
return
IF(MAX(Table1[Date])>=pre && MAX(Table1[Date])<=sele && MAX(Table1[Date])=maxdate,1,0) 

Capture.PNG

 

For more details, please check the pbix as attached.

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

@v-frfei-msft: Thank you so much, your solution perfectly worked. Smiley Happy

View solution in original post

5 REPLIES 5
v-frfei-msft
Community Support
Community Support

Hi @nikhil425,

 

You need to insert a date table into your data source firstly. Then you can use the date in the table to filter the table by creating a measure as below.

 

Measure = var sele = SELECTEDVALUE('Table'[Date])
var maxdate = CALCULATE(MAX(Table1[Date]),ALLEXCEPT(Table1,Table1[Category]))
var pre = DATEADD('Table'[Date],-3,MONTH)
return
IF(MAX(Table1[Date])>=pre && MAX(Table1[Date])<=sele && MAX(Table1[Date])=maxdate,1,0) 

Capture.PNG

 

For more details, please check the pbix as attached.

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

@v-frfei-msft: Thank you so much, your solution perfectly worked. Smiley Happy

@v-frfei-msft: Hello Friend,

 

My Team just updated the requirement. They wanted to find the missing categories from last 1year. In a sense I need to setup a slicer called Include Missing Catergories? (The values inside the slicer should have from last3 months, last6months, last9months, last12months). Depends on the user selection the report should go back and find the missing category. If they don't select any thing from the slicer then it should only the categories of the date selected (that is 3/31/2018). 

 

I did setup the Measures based on your idea, however I would like setup this slicers functionality and dynamically filter the categories in the table.  I added the measures but unable to attach the pbix file here. 

 

 

Thanks,

Nik

@v-frfei-msft Please find the screen shot below:

 

Pbix.JPG

Audi_B
Frequent Visitor

I posted a topic today basically requesting assistance for the same thing except for using a list report. I hope you get an answer!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors