Calculate highest value per period

[jwi1]

Hi all,

Hope someone can help me with the following.

Below is a simple start-table.

I am looking for a way to calculate the highest PART.PRICE per YEAR-MONTH for that particular PARTNUMBER_SUP (the outcome of the calculation is shown in the result table).

I also want to calculate the PART.PRICE for the YEAR-MONTHS, which are  not mentioned in the upper table (in this example: month 2021-4 and 2021-5).

The outcome must be the highest PART.PRICE of the latest YEAR-MONTH in which the PART.PRICE of that particular PARTNUMBER_SUP is mentioned (in this example month 2021-3: 58,28).

Thanks upfront for your kind assistance,

John

Start table:

invoice_date_year	invoice_date_month	partnumber_sup	part.price
2021	1	100043107	58,28
2021	1	100043107	58,28
2021	1	100043107	58,28
2021	2	100043107	58,28
2021	2	100043107	58,28
2021	2	100043107	58,28
2021	2	100043107	57,00
2021	2	100043107	58,28
2021	3	100043107	57,00
2021	3	100043107	57,00
2021	3	100043107	58,28
2021	3	100043107	58,28
2021	6	100043107	58,28
2021	6	100043107	58,28
2021	6	100043107	58,28
2021	6	100043107	58,28
2021	7	100043107	58,28
2021	7	100043107	63,23
2021	7	100043107	63,23
2021	8	100043107	63,23
2021	8	100043107	63,23

Result table:

invoice_date_year	invoice_date_month	partnumber_sup	part.price
2021	1	100043107	58,28
2021	2	100043107	58,28
2021	3	100043107	58,28
2021	4	100043107	58,28
2021	5	100043107	58,28
2021	6	100043107	58,28
2021	7	100043107	63,23
2021	8	100043107	63,23

[VahidDM · Accepted solution]

Hi @jwi1 

Add all columns to the table and change the aggragation of "part.price" column to Max.

If you need another table, use the below code to create a new table:

Table 2 =
SUMMARIZE (
    'Table',
    'Table'[invoice_date_year],
    'Table'[invoice_date_month],
    'Table'[partnumber_sup],
    "MaxPart Price", MAX ( 'Table'[part.price] )
)

Did I answer your question? Mark my post as a solution!

Appreciate your Kudos  !!

[ryan_mayu · Accepted solution]

@jwi1

then i think you need to modify the model.

pls see the attachment below

[ryan_mayu · Accepted solution]

@jwi1 

is this what you want?

Column = 
VAR _DATE=MAXX(FILTER('Table',Purchase[partnumber_sup]='Table'[partnumber_sup]&&'Table'[date]<='Purchase'[order_dat]),'Table'[date])
RETURN 
maxx(FILTER('Table',Purchase[partnumber_sup]='Table'[partnumber_sup]&&'Table'[date]=_DATE),'Table'[part.price])

[ryan_mayu · Accepted solution]

@jwi1 

pls try this

Table = 
VAR _time=ADDCOLUMNS(SUMMARIZE('date','date'[year],'date'[month]),"_date",date('date'[year],'date'[month],1),"d_y_m",CONCATENATE('date'[year] & " " , 'date'[month])
)
VAR tbl= ADDCOLUMNS(ADDCOLUMNS(ADDCOLUMNS(CROSSJOIN(dim,_time),"min",CALCULATE(min('listprijzen_per_maand'[invoice_date]),FILTER('listprijzen_per_maand',listprijzen_per_maand[partnumber_sup]=EARLIER('dim'[partnumber_sup])))),"scope",if([min]>[_date],"No")),"_price",
VAR p=maxx(FILTER('listprijzen_per_maand',dim[partnumber_sup]=listprijzen_per_maand[partnumber_sup]&&[_date]='listprijzen_per_maand'[invoice_date]),'listprijzen_per_maand'[part.price])
VAR _last=maxx(FILTER('listprijzen_per_maand',dim[partnumber_sup]=listprijzen_per_maand[partnumber_sup]&&[_date]>listprijzen_per_maand[invoice_date]),listprijzen_per_maand[invoice_date])
var P2=maxx(FILTER('listprijzen_per_maand',dim[partnumber_sup]=listprijzen_per_maand[partnumber_sup]&&_last=listprijzen_per_maand[invoice_date]),'listprijzen_per_maand'[part.price])
return if(ISBLANK(p),p2,p))
return SELECTCOLUMNS(FILTER(tbl,[scope]<>"No"),"Date_year_month",[d_y_m],"partnumber",dim[partnumber_sup],"Price",[_price])

[VahidDM · Accepted solution]

Hi @jwi1 

Add all columns to the table and change the aggragation of "part.price" column to Max.

If you need another table, use the below code to create a new table:

Table 2 =
SUMMARIZE (
    'Table',
    'Table'[invoice_date_year],
    'Table'[invoice_date_month],
    'Table'[partnumber_sup],
    "MaxPart Price", MAX ( 'Table'[part.price] )
)

Did I answer your question? Mark my post as a solution!

Appreciate your Kudos  !!

[jwi1]

Hi @VahidDM ,

Thanks for your reply.

Used the formula for a new table and it works fine for me.

As you can see, in the created table, months 4 and 5 are missing.

Is there a way to extend the formula, so the missing months are added automatically to the table with the MaxPart Price of the latest month (In this example the proce from mont 3: "5828")?

Thanks!

John

[ryan_mayu]

@jwi1 

you need to create a calendar table.

pls see the attachment below

[jwi1]

Hi @ryan_mayu ,

Thanks so far!

Seems like your calculation is taking the value of the previous partnumber.

See attachment below.

Can you give me some more advise please?

John

https://www.dropbox.com/s/9eo2izocjgsq212/highestvalue.pbix?dl=0

[ryan_mayu]

@jwi1 

is this what you want?

[jwi1]

@ryan_mayu ,

Almost there🙂

From partnumber_sup 100043107 I have deleted the data from month 2021-1 and 2021-2.

I have added for the same partnumber_sup the data from month 2020-10.

As you can see in the attachment the info from month 2021-1 and 2021-2 is missing now.

Is it possible to use the data from month 2020-12 to fill this gap?

John

https://www.dropbox.com/s/nnxz1k5npwygqcs/highestvalue.pbix?dl=0

[ryan_mayu · Accepted solution]

@jwi1

then i think you need to modify the model.

pls see the attachment below

[jwi1]

@ryan_mayu ,

I think this wwill do the job.

Thank you so much!

John

[ryan_mayu]

you are welcome

[jwi1]

Hi @ryan_mayu ,

I am struggling with below for a few days now, so decided to ask you to do me a last more favour.

I have added a new table in attached file: Purchase.

Looking at the combination of partnumber_sup and order_dat, the Test_value must be calculated for every order line.

Hope you can give me some guidance?!

Regards,

John

https://www.dropbox.com/s/berb4d2rjo5vsrm/highestvalue-v4.pbix?dl=0

[ryan_mayu]

@jwi1 

not clear about your new request. what's the expected output?

[jwi1]

@ryan_mayu ,

Hope this helps you: 

In the urchase table, purchase orders re mentioned.

every purchase order has an order date.

I want to add the Test_price to every partnumber in that particular order.

For example:

On 2-8-2021 we ordered part 100043107.

I want to add an extra column to the purchase table with the Test price.

In this example: 63.23.

This due to the fact the Test price part 100043107 in august 2021 is 63,23

[ryan_mayu · Accepted solution]

@jwi1 

is this what you want?

Column = 
VAR _DATE=MAXX(FILTER('Table',Purchase[partnumber_sup]='Table'[partnumber_sup]&&'Table'[date]<='Purchase'[order_dat]),'Table'[date])
RETURN 
maxx(FILTER('Table',Purchase[partnumber_sup]='Table'[partnumber_sup]&&'Table'[date]=_DATE),'Table'[part.price])

[jwi1]

@ryan_mayu ,

My first impression: works perfect!

Thanks a lot,

John

[ryan_mayu]

you are welcome

[jwi1]

Hi @ryan_mayu ,

Hope you are willing to give me one more advise.

If you look at the visual in the attachment: the info is coming from 3 different tables.

If I want to combine these 3 columns in 1 new table, is there a way to do so?

Thanks!

John

https://www.dropbox.com/s/iapjznegq73x5yl/highestvalue-v4.pbix?dl=0 

[ryan_mayu · Accepted solution]

@jwi1 

pls try this

Table = 
VAR _time=ADDCOLUMNS(SUMMARIZE('date','date'[year],'date'[month]),"_date",date('date'[year],'date'[month],1),"d_y_m",CONCATENATE('date'[year] & " " , 'date'[month])
)
VAR tbl= ADDCOLUMNS(ADDCOLUMNS(ADDCOLUMNS(CROSSJOIN(dim,_time),"min",CALCULATE(min('listprijzen_per_maand'[invoice_date]),FILTER('listprijzen_per_maand',listprijzen_per_maand[partnumber_sup]=EARLIER('dim'[partnumber_sup])))),"scope",if([min]>[_date],"No")),"_price",
VAR p=maxx(FILTER('listprijzen_per_maand',dim[partnumber_sup]=listprijzen_per_maand[partnumber_sup]&&[_date]='listprijzen_per_maand'[invoice_date]),'listprijzen_per_maand'[part.price])
VAR _last=maxx(FILTER('listprijzen_per_maand',dim[partnumber_sup]=listprijzen_per_maand[partnumber_sup]&&[_date]>listprijzen_per_maand[invoice_date]),listprijzen_per_maand[invoice_date])
var P2=maxx(FILTER('listprijzen_per_maand',dim[partnumber_sup]=listprijzen_per_maand[partnumber_sup]&&_last=listprijzen_per_maand[invoice_date]),'listprijzen_per_maand'[part.price])
return if(ISBLANK(p),p2,p))
return SELECTCOLUMNS(FILTER(tbl,[scope]<>"No"),"Date_year_month",[d_y_m],"partnumber",dim[partnumber_sup],"Price",[_price])

[jwi1]

Hi @ryan_mayu ,

So far so good!

However, see some double lines and also some lines do not have a price (BLANK or 0)

Is it possible to filter the double lines and BLANK or 0 - prices during the calculation of the table?

Hope this is my last question....can I sent you a pizza for your help?😊

John

[ryan_mayu · Accepted solution]

@jwi1 

i think in the sample file. we also have double lines in the price table.

i also tried to add a blank price in your sample file. It looks like the result does not changed.

pls see the attachment below

could you pls send me the your pbix file?

[jwi1]

Good day @ryan_mayu ,

You are right, my mistake.

Some parts are mentioned twice in the table, but with a different creditor.

Have added the column with the creditor in the table, works fine now.

The 0 and BLANK prices is also solved for now. It is simple, the price is not available.

Seems that for now, all is oke.

Many, many thanks for your help!

John