Extracting Dates from Totals

[TimK]

I am having problems extracting dates from a Measure

I attach my Pbix file HERE

The [Values] are multiplied by their corresponding inflation factor using a Measure called [Inflated Figure]

Tab [Page 2] basicaly shows the source data.....based on the [Inflated Figure] but aggregated up......

Tab [Page 3] is where i am trying to show the 'date' where the maximum value in the aggregated lines [Page 2] occur - for example [AA]&[L]&[One] the maximum value is 10,187.91 which occurs in 01/07/23

So on tab [Page 3] for [AA]&[L]&[One] i want to see 01/07/23

I am also trying then to create a new table [Summary Table] that basically contains the columns [DD], [P], [L/M] from the Descriptions table, and the [Max Date] value

Is this something anyone can help me with?

[Anonymous · Accepted solution]

Repalce Ste 3 Dax ... Change Max to min

Var MDate =

Var _DD = 'Summary Table'[DD]

Var _LM = 'Summary Table'[L/M]

Var _P = 'Summary Table'[P]

Var _price = 'Summary Table'[Maxvalue]

Var Output =

CALCULATE(

min('Summary Table'[Date]),

FILTER(ALL('Summary Table'),

'Summary Table'[DD]=_DD&&

'Summary Table'[L/M]=_LM&&

'Summary Table'[P]=_P&&

'Summary Table'[Total]=_price

)

)

Return

Output

[TimK]

ankitgogri - thank you very much for your very speedy help - really appreciate that

[Anonymous]

Step 1 
Create Summary Table 

Summary Table =

Var _Table =

SUMMARIZE(Data,

Data[Date],

'Description'[DD],

'Description'[L/M],

'Description'[P],

"Total",[Inflated Figure]

)

return

_Table

Step 2
add Column in above table (Find Max Value)

Maxvalue =

Var _DD = 'Summary Table'[DD]

Var _LM = 'Summary Table'[L/M]

Var _P = 'Summary Table'[P]

Var Output =

CALCULATE(

MAXx('Summary Table','Summary Table'[Total]),FILTER(All('Summary Table'),

'Summary Table'[DD]=_DD &&

'Summary Table'[L/M]=_LM&&

'Summary Table'[P]=_P

)

)

Return

Output

Step 3 add column in above table (fina Max date)

Var MDate =

Var _DD = 'Summary Table'[DD]

Var _LM = 'Summary Table'[L/M]

Var _P = 'Summary Table'[P]

Var _price = 'Summary Table'[Maxvalue]

Var Output =

CALCULATE(

MAX('Summary Table'[Date]),

FILTER(ALL('Summary Table'),

'Summary Table'[DD]=_DD&&

'Summary Table'[L/M]=_LM&&

'Summary Table'[P]=_P&&

'Summary Table'[Total]=_price

)

)

Return

Output



Step 4
create new 

Final Summary Table =

SUMMARIZE('Summary Table',

'Summary Table'[DD],'Summary Table'[L/M],'Summary Table'[P],"Total",sum('Summary Table'[Total]),"max_Vlaue",MAX('Summary Table'[Maxvalue]),"Maxdate",MAX('Summary Table'[Var MDate]))

FYI Max value for [DD], [P], [L/M]  = AA| L | One is 11814.85 as per your page 2 not 10,187.91 on 01/07/23
refer below screenshot

[TimK]

Thank you ankitgogri

I have attached my latest file HERE

You will see on the [Summary Table] as filtered.......

For [AA],[L], [One] the max value is correct as 11,814.85

However there are 2 dates when this figure occurs:  01.04.29 and 01.09.29

Is it possible to somehow show the first occurence if there are duplicates?

So I would need the date 01.04.29 rather than 01.09.29

Many thanks

[Anonymous]

With a combination of AA]&[L]&[One] you have two ID 1 and 45 

use the following Code in your Summary Table

Thanks

[TimK]

hi ankitgogri

Thanks for this........sorry i dont think i explained it correctly.....

So with the data in the model, i do not want to look at the maximum values at ID level; i want them at the aggregated [DD], [P], [L/M] level - so the total for this row is 56,661.99 and the maximum value in the row is 10,187.91 on 01/07/23

Does that make sense?