Forum Discussion

rob_vander2's avatar
rob_vander2
Icon for Helper II rankHelper II
10 months ago
Solved

sum distinct values for each id for latest date

Hi All, 

 

I have below dataset

 

IDDateSubIDValue
101/01/2025a100
102/01/2025a20
102/01/2025b20
201/01/2025a100
202/01/2025a50

 

For ID 1, on latest date I have two records with same value, so I need to pick only 20. Output should be below

 

 ID Value
 120
 250
 Total70
  • parry2k's avatar
    parry2k
    10 months ago

    rob_vander2 try this measure:

     

    Sum Latest = 
CALCULATE ( 
    SUM ( 'Table Latest'[Value] ),
    INDEX ( 
        -1,
        ALLSELECTED ( 'Table Latest'[ID], 'Table Latest'[SubID], 'Table Latest'[Date], 'Table Latest'[Value] ),
        ORDERBY ( 'Table Latest'[Date] ),
        PARTITIONBY ( 'Table Latest'[ID] )
    )
)

     

     

     

6 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    10 months ago

    rob_vander2 try this measure:

     

    Sum Latest = 
CALCULATE ( 
    SUM ( 'Table Latest'[Value] ),
    INDEX ( 
        -1,
        ALLSELECTED ( 'Table Latest'[ID], 'Table Latest'[SubID], 'Table Latest'[Date], 'Table Latest'[Value] ),
        ORDERBY ( 'Table Latest'[Date] ),
        PARTITIONBY ( 'Table Latest'[ID] )
    )
)

     

     

     

    • v-menakakota's avatar
      v-menakakota
      Icon for Community Support rankCommunity Support
      10 months ago

      Hi rob_vander2 ,
      Thanks for reaching out to the Microsoft fabric community forum.

       

      I would also take a moment to thank  parry2k  , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference. 
      I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you.

      Best Regards, 
      Community Support Team 

      • v-menakakota's avatar
        v-menakakota
        Icon for Community Support rankCommunity Support
        10 months ago

        Hi rob_vander2 ,

        I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you .

         

        Best Regards, 
        Community Support Team  

  • TanishJain's avatar
    TanishJain
    New Member
    10 months ago

    Hey Rob 

    If i had such dataset then i would first split the column of date into 2 where once column we will have the date and 2nd the alphabet which is at the end and then creat the matrix where my row would be ID and make the mesure for the Value where i would calculate it it using dax function like calculate/TOPN/etc there are mutiple ways plese let me know if you want the dax.

    And in case you want to make the table clean in power query then i have attached the image for your reference ther can be other way to solve it but this is the one i got output with 
    Advance editor -
    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIw1AciIwMj00Qgz9DAQClWBypjhCxjhE0iCSFhhNMsIwyzTIESsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Date = _t, SubIDValue = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Date", type text}, {"SubIDValue", Int64.Type}}),
    #"Split Column by Position" = Table.SplitColumn(#"Changed Type", "Date", Splitter.SplitTextByRepeatedLengths(10), {"Date.1", "Date.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Date.1", type date}, {"Date.2", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type1", {"ID"}, {{"max of date", each List.Max([Date.1]), type nullable date}, {"Min of alphabet", each List.Min([Date.2]), type nullable text}, {"Value", each _, type table [ID=nullable number, Date.1=nullable date, Date.2=nullable text, SubIDValue=nullable number]}}),
    #"Expanded Value" = Table.ExpandTableColumn(#"Grouped Rows", "Value", {"Date.1", "Date.2", "SubIDValue"}, {"Value.Date.1", "Value.Date.2", "Value.SubIDValue"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Value", "flag", each if [max of date]=[Value.Date.1] and [Min of alphabet]=[Value.Date.2] then "Yes" else "No"),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([flag] = "Yes")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Value.Date.1", "Value.Date.2", "flag"})
    in
    #"Removed Columns"

     

    If need any clarification please feel free to reachout to me 🙂

    Thank You