Sum Based on criterias - MAX Date, LastNonBlankValue... etc

[brunofds]

Hello there! A simple question that is twisting my mind.

I have a Fact Table, linked with a dimDate:

CUSTOMER     CustomerID         Sales                                             Date

A	a1	R$ 1.500,	01/02/2021
A	a2	R$ 1.500,	02/02/2021
A	a3	R$ 1.500,	03/02/2021
A	a4	R$ 1.500,	04/02/2021
A	a5	R$ 1.500,	05/02/2021
A	a6	R$ 1.500,	06/02/2021
A	a7	R$ 1.500,	07/02/2021
B	b3	R$ 2.500,	03/02/2021
B	b4	R$ 2.500,	04/04/2021
B	b5	R$ 2.500,	05/02/2021
A	a1	R$ 1.500,	01/02/2022
A	a2	R$ 1.500,	02/02/2022
A	a3	R$ 1.500,	03/02/2022
A	a4	R$ 1.500,	04/02/2022
A	a5	R$ 1.500,	05/02/2022
A	a6	R$ 1.500,	06/02/2022
A	a7	R$ 1.500,	28/02/2022
B	b3	R$ 2.500,	03/02/2022
B	b4	R$ 2.500,	30/04/2022
B	b5	R$ 2.500,	05/02/2022

I need to sum the "sales" column based on Customer, but with two conditions:

1 - The Date should be the most recent one for the year level.

2  The sum of sales computed must be just one single value, ignoring the repeated ones.

This will be shown on a matriz (Product Level) and also with a card, with DimDate[Year] as slicers.

For instance, in a matrix, this should appear:

Without any filters on Date[Year]:

*****EDIT**** THE TOTAL EXPECTED IS 8000********

The first part is simple, i just did:

Measure1=

var MaxDate = MAX(FactTable[Date])

RETURN

CALCULATE ( SUM(Fact[Sales]), FILTER( FactTable, 'FactTable[Date] = MaxDate )

But of course, this sums up every row on Sales[Column], hence wrong.

I've tried to put LASTNONBLANK/LASTNONBLANKVALUE as a parameter in calculate, which appeared to be correct at first, but wrong in the Total level and in the year filter applied.

Measure2=

VAR MaxDate =  MAX(FactTable[Date])

VAR FILTERZ = FILTER ( 'FactTable,  'FactTable = MaxDate )

RETURN

           CALCULATE (

            LASTNONBLANKVALUE (

                FactTable[Sales],

                SELECTEDVALUE (FactTable[Sales] )

            ), FILTERZ

           )

***********************************************

What am i missing?

[Anonymous · Accepted solution]

Hi @brunofds ,

According to your description, you want to calculate the sum based on criterias. Here are my steps you can follow as a solution. 

(1)This is my test data.  

(2)We can create  measures.  

Measure =  

var maxdate = CALCULATE(MAX('Table'[Date]), ALLSELECTED('Table'),VALUES('Table'[CUSTOMER])) 

var _id = CALCULATE(MAX('Table'[CustomerID]), FILTER('Table', 'Table'[Date] = maxdate)) 

return CALCULATE(SUM('Table'[Sales]), FILTER('Table', 'Table'[CustomerID] = _id)) 

Total_M = IF( ISFILTERED('Table'[CUSTOMER]), [Measure], SUMX(VALUES('Table'[CUSTOMER]), [Measure])) 

(3)Then the result is as follows.  

If this method does not meet your needs, you can provide us with detailed input and output examples in tabular form so that we can better solve the problem for you. 

Best Regards, 

Neeko Tang 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.  

[brunofds]

Unfortunately, your solution does not work because my dataset has somehow "duplicated rows" -- it's not exactly this, but its the easiest way to explain it.

So your code would sum those "duplicated rows" -- same Customer Name & CustomerID.

[brunofds]

Please, check the data set on this link: https://easyupload.io/wi7jfp

(M Code provided a the the bottom)

 

The expected result for 2022 is the sum of yellow lines, and for 2021 the sum of green lines.

Criteria:

- Consider only the most recent date for each customer.

- Consider only a single value per customer

*******

M CODE FOR SAMPLE DATA:

M Code:

let
    Fonte = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tZfbbtswDIZfRQh26TEideSl2ziDgSQekjQYWvT9X2OU7KJYbGey1wXwhSP7Mw+/SOrtbYNOfsREtKk2SEDRA1u71XJbn3ftUV3a5lTL3fmbQgOWHLigK4vyl/ZbzVvS8vJ7NbAMWT3BOj4152unDt1T17OMBUcevPeVDWNWDDEE7QUpa2TluwwcMbNeGtX+bNRetW3PchCdARcF5dPjvNXh3ixGk9Z8BG8NBHIZpZ4PL0/q2O3UgEIG77RcpqJkFpoJVnA5XFo4HMFQzKzntj6otr4c29PACmAZwRiu0A8ujlgmxjBmJdStvdWH3WCXkbBLCOT5inmGRcwupjUGzQaiPJBYPcAK24strmKTnpG0uHtbrMa0RhqILQTbv39Q+0N3bk6vrx8yEI72IAmq+vRIjOiepV3o4wfOOiD0mbXrJDptJ5n78Au0TalDybC4lsKEOIXzmudxt8U45+MYd/o0byku2ke421c4+4n7tdhZM8Z1n7ylOKZHuKXOlgmFQDYK6OAEF6uY97loGFcKpRhXJpRyXJFQ/s3ZkVAWOFsilHJckVAKcLkZaG1ytUHQaAG9zrhr3arvuYLjUJ20VDoGE0MVcKh0E6atFh1tMWsYv0Z0k7j1opvGrRZdubNFoptxdq3opnGrRTeBo2gI+5aKOsrViy7NRbtaEnsYWqIn8NJeozVV7q5TKDZZv4Ly0kGt7Tv95Vqrbr9vn5vLHYrmUYFtdtJCpITq56JO/Xhpj/W5HqHmrSreCRIpI0OBlm2V1ZQGlbhyHziBcZABUCaIQLOwwl0gG965AES2yrmagZV1aE4zkgYmrPIstcDN0Q7wQUupksEuxeyBlyXytw4hcu9lP6RNw4rEn/QXdJCJkh6GbLEy8sf5C4UxDVspjBnYOmEscPOvwpjzcpUwZmDrhHEPW9KIWQ6AAXz8GP7km/6/oPqzIrLJh590uJO65zmjfjRq16muuVybIZFSEhFIzmQ6q1v/adWS8kpBGoIEy2Bl+vNgP2kgb97ffwM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CONTRATO = _t, Customer = _t, #"Customer Hierarchy / ID" = _t, Sales = _t, Date = _t]),
    #"Tipo Alterado" = Table.TransformColumnTypes(Fonte,{{"CONTRATO", Int64.Type}, {"Customer", type text}, {"Customer Hierarchy / ID", type text}, {"Sales", Currency.Type}, {"Date", type date}})
in
    #"Tipo Alterado"