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]

 

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?

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

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

     

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

    use the following Code in your Summary Table


    Thanks

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

      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?

  • 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

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

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