Calculate last 12 months of sales and use it historically

[Morten_DK]

Hi,

I need to calculate the last 12 months of sales per material number, but I need to calculate it based on today and then 12 months back. This means that it has to be calendar/time dimension independent. 

I tried to do something like:

Sales12M = CALCULATE (
    [Sales QTY];
    DATESBETWEEN (
        Calendar[Date];
        NEXTDAY ( SAMEPERIODLASTYEAR ( Today() ) );
        Today()
    )
)

But that doesn't work as I get the following error: The first argument to 'SAMEPERIODLASTYEAR' must specify a column.

Furthermore DATESBETWEEN in above points to the Calendar table, but that doesn't make sense in this case, so perhaps I am not on the right path at all.

The reason for why I need to use the calculation calendar independent is that I need to see the sales prices per material number in the last two years, but I need the quantity from the last 12 months to make a kind for weighted price. 

As a table I would like the following:

Period                 Material       Sales Price     Sales12M     Weighted Price

2016-01            A                 100                2                 200

2016-02            A                 110                2                 220

...

2017-01            A                 200                2                 400

[Morten_DK]

Hi both,

I have tried in my Calendar table to add the column "Today = Today()" and then used that one in the formula as follows:

Sales12M = CALCULATE (
[Sales QTY];
DATESBETWEEN (
Calendar[Date];
NEXTDAY ( SAMEPERIODLASTYEAR ( 'Calendar'[Today]) );
Today()
)
)

It does not return any errors, but the result is that I get the Sales QTY for all periods, not only the last 12 months. Other filters are reflected though.

I then tried to move the Today column to my 'Movments' table and made the following formula:

Sales12M = CALCULATE(
[Sales QTY];
FILTER(
ALL('Movements');
Movements[Date]>=DATEADD(Movements[Today];-1;YEAR) && Movements[Material] = Max(Movements[Material])))

That however returns all periods Sales QTY and also does not reflect filters from other tables.

I think the issue is that I have a calendar table and a material number table that is connected to the 'Movements' table where the Sales QTY is comming from.

[v-qiuyu-msft]

Hi @Morten_DK,

Are you able to achieve your requirement now? If not, would you please share sample .pbix about Movements and Calendar table for our analysis?

Best Regards,
Qiuyun Yu

[v-qiuyu-msft]

Hi @Morten_DK,

In your scenario, you can create a calculated column Today = TODAY(). Then create a measure like this:

Sales12M = CALCULATE(SUM(Table1[Sales QTY]),FILTER(ALL(Table1),'Table1'[Period]>=DATEADD('Table1'[Today],12,MONTH) && 'Table1'[Material]=MAX('Table1'[Material])))

Best Regards,
Qiuyun Yu

[Phil_Seamark]

Have you tried creating a calculated column using the TODAY() function and using that in your SAMEPERIODLASTYEAR function?