Forum Discussion

TimK's avatar
TimK
Icon for Helper III rankHelper III
4 years ago
Solved

Extracting Dates from Totals

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]   ...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago
    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
Anonymous's avatar
Anonymous
Not applicable
4 years ago

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's avatar
TimK
Icon for Helper III rankHelper III
4 years ago

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