Forum Discussion

novicenovice's avatar
novicenovice
Helper 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 get the expected result like the chart below.

 

In advance thanks for your help

 

DATA SOURCESUM OF LATEST VALUESCHART RESULT 

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

3 Replies

  • 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

      • 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]
        )