MaxDate per Value

[BhagatRansi]

Hi everyone,

I am bit in a challenge. I am receiving a table where measures a different frequencies. Some are delivered on a monthly base and some on random dates. The client like to see the most current value with the according date as an informtional attribute with it.

Following an example of the data structure:

Date	Organisation	Value A	Value B	Value C	Value D
01.01.2021	West	50	20	50	60
01.02.2021	West	30		50	60
01.03.2021	West	30	50	50	60
01.04.2021	West	30		50	60

Result:

For Value A,C & D the MaxDate is 01.04.2021 with the Value A=30; C=50 & D=60

For Value B the MaxDate is 01.03.2021 with the Value B=50

How can I create a measure showing me exact these values and also map the accoring attributes like Date and Organisation to it?

Looking forward to hear from you

Thanks in advance for any idea

[ryan_mayu · Accepted solution]

@BhagatRansi 

1. select date and orgnization column and unpivot other columns in PQ

2. create two measures

maxdate = MAX('Table'[Date])

Measure = MAXX(FILTER('Table','Table'[Attribute]=max('Table'[Attribute])&&'Table'[Date]=max('Table'[Date])),'Table'[Value])

 please see the attachment below

[BhagatRansi]

Hi Ryan_mayu,

I still need your support the solution works so far but now I am facing the issue that when summarizing the values I am getting the Max Value of the analyzed dimension.

See in your example Value D = 60 and the Total result is also 60.

Is there a way to sum or to calculate the average of the values? In our case Total =47,5

[ryan_mayu]

@BhagatRansi 

you can create another measure

Measure 2 = AVERAGEX(VALUES('Table'[Attribute]),[Measure])

[BhagatRansi]

Thanks a lot just figured it out last night 😅

Step by step I am getting into it

[selimovd]

Hey @BhagatRansi ,

I guess you need anyway a new measure for each Value.

The following should work:

Last Value A = CALCULATE( SUM( myTable[Value A], myTable[Date] = MAX( myTable[Date] ) )

Let me know if that works for you.

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

[BhagatRansi]

Thanks for your reply this solution does not work as MAX cannot be used as filter expression in the Calculate formula