cancel
Showing results for
Did you mean:
Frequent Visitor

How to find value for most recent date of the quarter

Hi,

I have below table:

 artikel date preis year quarter 1101692 20.01.2017 75,4 2016 1 1101692 21.01.2017 92,58 2016 1 1101692 20.04.2017 75,4 2017 2 1101692 25.04.2017 92,58 2017 2 1101693 20.01.2017 92,58 2017 1 1101693 26.01.2017 75,4 2017 1

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:

 1101692 21.01.2017 92,58 2016 1 1101692 25.04.2017 92,58 2017 2 1101693 26.01.2017 75,4 2017 1

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

4 REPLIES 4
Frequent Visitor

Re: How to find value for most recent date of the quarter

I have tried the solution provided in

https://community.powerbi.com/t5/Desktop/Latest-Value/m-p/101331#M42482

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 ??

Microsoft

Re: How to find value for most recent date of the quarter

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

Resolver II

Re: How to find value for most recent date of the quarter

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

Microsoft

Re: How to find value for most recent date of the quarter

Hi @NewbeePowerBI,

Best Regards,
Angelia

Announcements

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors