Forum Discussion
MaxDate per Value
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
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
6 Replies
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
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
you can create another measure
Measure 2 = AVERAGEX(VALUES('Table'[Attribute]),[Measure])
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 regardsDenisBlog: WhatTheFact.biThanks for your reply this solution does not work as MAX cannot be used as filter expression in the Calculate formula
