Versioning KPI month-by-month & dynamic sum calculation

[Anonymous]

Hello all, 

I'm looking for your help because I don't know the way forward in the creation of a stock KPI report. 

What I'm trying to achieve : the evolution of my stock quality KPI month by month based on the sales history for each product over the past 12 months (there are more calculation but my problem lies here) 

My issue: Creating the relationship between by table stock and my table sales and effectively calculate my sales history over the last 12 months from the extraction date, as I have one new version a month I'm not sure how to link them together now. 

Here are my two tables: 

1* My stock table, for which I retrieve a 'snapshot' each first Monday of the month : 

Product	Location	Stock	Extraction date
A	AB	3	07-01-19
B	AC	4	07-01-19
C	AB	2	07-01-19
D	AD	5	07-01-19
A	AB	5	04-02-19
B	AC	7	04-02-19
C	AB	1	04-02-19
D	AD	2	04-02-19
A	AB	1	04-03-19
B	AC	2	04-03-19
C	AB	1	04-03-19

2* My sales history table

Product	Location	Date	Quantity
A	AB	10-12-18	1
B	AC	10-03-18	2
C	AD	05-09-18	1
A	AB	08-01-18	3
B	AC	29-09-18	5
D	AD	06-02-19	3
C	AD	04-03-18	2
A	AB	14-05-18	1
D	AC	04-07-18	2
B	AC	14-05-18	1

So for each product/location/extraction date I would like to get the sum of the sales history over the past 12 month (start date = extraction date - one year / end-date = extraction date). I would like to make it dynamic, next month I can refresh my report and get the info for this month. 

Accoding to you, what would be the most effective way to do it ? 

Thanks a lot for your help!
Reno

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[v-frfei-msft · Accepted solution]

Hi @Anonymous ,

We can create a measure as below.

Measure = 
VAR startdate =
    MAX ( stock[Extraction date] ) - 365
VAR prod =
    MAX ( stock[Product] )
VAR location =
    MAX ( stock[Location] )
RETURN
    CALCULATE (
        SUM ( sales[Quantity] ),
        FILTER (
            sales,
            sales[Product] = prod
                && sales[Location] = location
                && 'sales'[Date] >= startdate
                && 'sales'[Date] <= MAX ( 'stock'[Extraction date] )
        )
    )

[Anonymous]

@v-frfei-msft Nevermind, by addind the measure to my model I realized how the relationship was made within the measure via the filters. This is amazing ! What is the purpose of the MAX function here ? I don't really knows what it makes with text column. Anyway thanks again !

[v-frfei-msft]

Hi @Anonymous ,

The MAX  function is used to get the value of current row in the column here.

[Anonymous]

@v-frfei-msft Thanks a lot for your answer, the output looks exactly what I'm looking for. 

I'm really surprise to see that there is no relationship between the two tables, how is that possible ? I'm a bit confused 🙂 

@Ashish_Mathur Thanks for your answer, interesting model with those extra tables. However the output is not exactly what I wanted. Date column should be the extraction one, and I should be able to compare sales history of the last 12 month with the current stock (in order to make an average sales history per day, to measure how many days can be covered by the current stock).