Group by all columns and filter on Max Date

[nikhil425]

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:

Category	Date	Company	Current Value	Current Cost	Sector	Industry Group	Holding Co Domicile	Geography
Crude1	3/31/2018	Crude Limited	58938.83	3293682.25	20 -Industrials	2010 - Capital Goods		Italy
Crude1	12/31/2017	Crude Limited	60086.89	3293682.25	20 -Industrials	2010 - Capital Goods	N/A	Italy
BellsA	3/31/2018	Bell Limited	1421132.85	1333585.91	40- Financials	4020 - Diversified Financials		India
BellsA	12/31/2017	Bell Limited	1592073.46	1337505.94	40- Financials	4020 - Diversified Financials	N/A	India
Colsz	3/31/2018	Col Limited	6313251.39	2536439.53	40- Financials	4020 - Diversified Financials		German
Colsz	12/31/2017	Col Limited	6321785.58	2496110.25	40- Financials	4020 - Diversified Financials	N/A	German
MindsF	3/31/2018	Minds Limited	2972144.32	2331269.01	40- Financials	4020 - Diversified Financials		France
MindsF	12/31/2017	Minds Limited	3413565.81	2887832.68	40- Financials	4020 - Diversified Financials	N/A	France
TetraS	12/31/2017	Tetrs Power	8045424.48	4270681.08	40- Financials	4020 - Diversified Financials	N/A	Austria
BlissN	3/31/2018	Bliss Limited	6212173.43	8678780.89	40- Financials	4020 - Diversified Financials		UK
BlissN	12/31/2017	Bliss Limited	6296859.41	8508269.9	40- Financials	4020 - Diversified Financials	N/A	UK

Desired Output. 

Category	Date	Company	Current Value	Current Cost	Sector	Industry Group	Holding Co Domicile	Geography
Crude1	3/31/2018	Crude Limited	58938.83	3293682.25	20 -Industrials	2010 - Capital Goods		Italy
BellsA	3/31/2018	Bell Limited	1421132.85	1333585.91	40- Financials	4020 - Diversified Financials		India
Colsz	3/31/2018	Col Limited	6313251.39	2536439.53	40- Financials	4020 - Diversified Financials		German
MindsF	3/31/2018	Minds Limited	2972144.32	2331269.01	40- Financials	4020 - Diversified Financials		France
TetraS	12/31/2017	Tetrs Power	8045424.48	4270681.08	40- Financials	4020 - Diversified Financials	N/A	Austria
BlissN	3/31/2018	Bliss Limited	6212173.43	8678780.89	40- Financials	4020 - Diversified Financials		UK

[v-frfei-msft · Accepted solution]

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) 

For more details, please check the pbix as attached.

Regards,

Frank

[v-frfei-msft · Accepted solution]

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) 

For more details, please check the pbix as attached.

Regards,

Frank

[nikhil425 · Accepted solution]

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

[nikhil425]

@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

[nikhil425]

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

[Audi_B]

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