Get the price from a period and recalculate the sales within a range

[daninemeth]

Hi Guys,

I'm having troubles to make a measure for the following problem:

I have 2 tables,

the first is the sales table (containing billed amount of a product on a date)

sales table

The second is my price table, containing the period prices of the produkt.

price table

The main task, how can I retrieve the Price from the Pricing table(I only have periods here), based on the Sales Table - Billing Date (these are exact dates)? And I'm only interested in the last 1 year, based on the latest Price Start Date.

I.e. : Produkt A - has a latest Price Start Date from 01/11/2021, so I only want to see the results from 01/10/2020 on the Billing Side.

Thanks for your help in advance!

Dani

[mangaus1111]

Hi @daninemeth ,

see my pbi file

https://1drv.ms/u/s!Aj45jbu0mDVJizHMCAOklnQ074Xt?e=fhY9CK

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

[daninemeth]

Hi @mangaus1111 ,

this is great! Amazing. Do you know the solution for the other direction? So, I'd like to see the price what was one year ago compared to that produkt latest price date, for every period.

Thanks for your help!

Dani

[mangaus1111]

sorry I don´t understand this second question

[daninemeth]

Hi @mangaus1111 ,

sorry. I try with results:

so lets check what was the latest price start date for Product A and take the price what was 1 year ago based on latest price date and insert the price in to the sales table up to the the billing date is less or equal the latest price date.

[mangaus1111]

Hi @daninemeth , can you please show your exprected result in the Sales Table?

[daninemeth]

Hi @mangaus1111 ,

sure!

Basicaly, get the sales price from the correct period based on billing date and if the the billing date is a year older based on the latest start price return zero/ empty.

 Thanks!

Dani