Forum Discussion

novicenovice's avatar
novicenovice
Icon for Helper I rankHelper I
7 years ago
Solved

SUM THE LATEST VALUES

Dear all,   I need your help, I would like to make a chart on which for each date I want to get the sum of the lastest values whatever the customer.   I try some formulas (sumx, maxx) but cannot ...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    Hi novicenovice,

     

    You can use below measure to achieve your requirement:

    Last Total =
VAR currDate =
    MAX ( 'DateTable'[Date] )
VAR summary =
    SUMMARIZE (
        FILTER ( ALLSELECTED ( Table ), [Input Date] <= currDate ),
        [Customer],
        "LastDate", MAX ( Table[INPUT DATE] )
    )
RETURN
    SUMX (
        CALCULATETABLE (
            Table,
            INTERSECT (
                SELECTCOLUMNS ( Table, "CUSTOMER", [CUSTOMER], "Date", [INPUT DATE] ),
                summary
            )
        ),
        [VALUE]
    )

     

    Regards,

    Xiaoxin Sheng

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi novicenovice,

 

You can use below measure to achieve your requirement:

Last Total =
VAR currDate =
    MAX ( 'DateTable'[Date] )
VAR summary =
    SUMMARIZE (
        FILTER ( ALLSELECTED ( Table ), [Input Date] <= currDate ),
        [Customer],
        "LastDate", MAX ( Table[INPUT DATE] )
    )
RETURN
    SUMX (
        CALCULATETABLE (
            Table,
            INTERSECT (
                SELECTCOLUMNS ( Table, "CUSTOMER", [CUSTOMER], "Date", [INPUT DATE] ),
                summary
            )
        ),
        [VALUE]
    )

 

Regards,

Xiaoxin Sheng

  • novicenovice's avatar
    novicenovice
    Icon for Helper I rankHelper I
    7 years ago

    Thanks Anonymous, it works perfectly.

    :smileyhappy:

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Anonymous 

       

      Hi, 

       

      I have the same issue (https://community.powerbi.com/t5/Desktop/summing-the-latest-entry-by-date/m-p/859750#M412467 topic is currently hidden for spam)

       

      I've tried your forumula but it's only summing the values from the specific date 

       

      I am using <= currDate

       

      table on the left is the date ( I am using BRANCH KEY rather than CUSTOMER in OP). Table on the right is the date from the definition date table along with your measure, line graph on the right is the visualisation. Any idea what might be going wrong?

       

       

      Last Total =
      VAR currDate = MAX ( 'Period Definition'[Date])
      VAR summary =
      SUMMARIZE(
      FILTER ( ALLSELECTED('Stockloss Reporting Dynamic Date table') , 'Stockloss Reporting Dynamic Date table'[Date] <= currDate), 'Stockloss Reporting Dynamic Date table'[Branch Key Real]
      ,
      "LastDate", MAX ( 'Stockloss Reporting Dynamic Date table'[Date])
      )
      RETURN
      SUMX (
      CALCULATETABLE (
      'Stockloss Reporting Dynamic Date table',
      INTERSECT (
      SELECTCOLUMNS ( 'Stockloss Reporting Dynamic Date table', "Branch Key Real", 'Stockloss Reporting Dynamic Date table'[Branch Key Real], "Date", 'Stockloss Reporting Dynamic Date table'[Date] ),
      summary
      )
      ),
      'Stockloss Reporting Dynamic Date table'[FINAL ANNUALISED ADJ VAL FIGURE]
      )