How to find value for most recent date of the quar...

NewbeePowerBI · ‎08-28-2017

Hi,

I have below table:

and I need to produce result like this i.e. i want to group by artical number, year, quarter and value of most recent date of the quarter:

I tried something like this but it is giving me same value for preis column. What am I doing wrong?

Table = SUMMARIZE(Test;Test[artikel];Test[year];Test[quarter];"Maxdate"; MAX(Test[date]);"preis";MAX(Test[preis]))

Anonymous · ‎08-29-2017

Hi,

You could try adding a calculated column which is a flag for latest date within the quarter:

Latest_Flag = IF( [date] = Calculate(MAX([date], Filter(Table, year = earlier(year) && quarter = earlier(quarter))), "Latest", "Not")

Then you can summarize filtering on Latest_Flag = "Latest"

Calculate(Summarize(Test;Test[artikel];Test[year];Test[quarter]; Test[date]), Test[Latest_Flag] = "Latest")

Unfortunately I don't have access to PBI to test this exactly but I believe this sort of logic should do the trick.

Thanks,

Will

NewbeePowerBI · ‎08-28-2017

I have tried the solution provided in

but it needs to steps and as my data sets are very big, join is taking too much time. Can we do it only with MAX ??

v-huizhn-msft · ‎09-05-2017

Hi @NewbeePowerBI,

Have you resolved your issue? If you have, please mark the right or helpful reply as answer.

Best Regards,
Angelia

v-huizhn-msft · ‎08-29-2017

Hi @NewbeePowerBI,

1. Create a new table using the formula below.

Table = SUMMARIZE(Test,Test[artikel],Test[year],Test[quarter],"Maxdate",MAX(Test[date]))

2. In new table and Test table, create a unique column, and create relationship between the column.

Column = Test[artikel]&Test[year]&Test[quarter]&Test[date]

Column = 'Table'[artikel]&'Table'[year]&'Table'[quarter]&'Table'[Maxdate]

3. Create a calculated column to get the [preis] value.

pre = RELATED(Test[preis])

Best Regards,
Angelia

How to find value for most recent date of the quarter