Sum of latest values of all groups

[Anonymous]

Hello,

I have a data from database from where I pull out the latest values. After a long googling session I managed to create a measure, which pulls out the latest values 

Gross Sales (EUR) = 
VAR __myLastAmount = CALCULATE(LASTNONBLANK(Sales[Gross Sales LE (EUR)], ""), FILTER('Sales', Sales[EntryDate] = MAX(Sales[EntryDate])))
VAR __tmpTable = SUMMARIZE('Sales',[CustID], [Year], [ProductType], [Season],"__LastAmount",__myLastAmount)
RETURN SUMX(__tmpTable,[__LastAmount])

I group the data by CustID, Year, ProductType, Season and then find the latest GrossSales for the group based on EntryDate, which is datetime timestamp. Getting the latest values like this works, however I cannot find a way to get a total sum for these values.

Does anyone know how to do this?

Best regards,

Ales

[amitchandak]

Try Something like this

sumx(  
        ADDCOLUMNS( 
            SUMMARIZE(
                Invoices;
                Invoices[Invoice ID]
            );
            "_Measure";
            calculate(
                sum(Invoices[Amount]);
                filter(
                    ALL(Invoices);
                    Invoices[Invoice ID]= earlier(invoices[Invoice ID])
                )
            )
        )
        ;[_Measure]
    )

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[amitchandak]

Or try like

Calculate(
    sumx(  
        ADDCOLUMNS( 
            SUMMARIZE(
                data,
                data[NUM_DPL],
				"max_date",
				MAX( 'Date'[Date]),
                "Max_ID", max(data[NUM_DPL])
            ),
            "Over20K",
            calculate(
                Max(data[VLR_SLD_TOT_CAL]),
                filter(
                    all(data),
                    data[NUM_DPL]= [Max_ID] && data[DAT_REF]= ([max_date]) )
                )
            )
        ,[Over20K]
    )
)

[rajulshah · Accepted solution]

Hello @Anonymous,

You can create a measure as below:

Total Gross Sales = 
VAR __tmpTable = SUMMARIZE('Sales',[CustID], [Year], [ProductType], [Season],"__LastAmount",__myLastAmount)
RETURN SUMX(__tmpTable,[Gross Sales (EUR) ])

Let me know if this didn't work. 

[Anonymous]

it works, thank you!!