PREVIOUSMONTH in DIRECTQUERY, for DIFFERENT Products

[jvalencia]

Based on the next example of Data:

PRODUCT	DATE_X	SALES
AAAA	1/1/2018	1000
AAAA	2/1/2018	1100
AAAA	3/1/2018	1200
AAAA	4/1/2018	1300
BBBB	1/1/2018	1400
BBBB	2/1/2018	1500
BBBB	3/1/2018	1600
BBBB	4/1/2018	1700
CCCC	1/1/2018	1800
CCCC	2/1/2018	1900
CCCC	3/1/2018	2000
CCCC	4/1/2018	2100

I am trying to get the data from previous month, per Product, So I used the Following formulas:

    Sales All=SUM('data'[SALES])     *created a Meausre

    Sales PM=CALCULATE([Sales ALL], PREVIOUSMONTH(Date)   *created a Meausre

Thus, hoping to obtain the following table

PRODUCT	DATE_X	SALES	SALES PM
AAAA	1/1/2018	1000
AAAA	2/1/2018	1100	1000
AAAA	3/1/2018	1200	1100
AAAA	4/1/2018	1300	1200
BBBB	1/1/2018	1400
BBBB	2/1/2018	1500	1400
BBBB	3/1/2018	1600	1500
BBBB	4/1/2018	1700	1600
CCCC	1/1/2018	1800
CCCC	2/1/2018	1900	1800
CCCC	3/1/2018	2000	1900
CCCC	4/1/2018	2100	2000

However, It is not filtering per Product, so it is basically Suming the Sales for ALL Products, SO I am having this result:

PRODUCT	DATE_X	SALES	SALES PM
AAAA	1/1/2018	1000
AAAA	2/1/2018	1100	4200
AAAA	3/1/2018	1200	4500
AAAA	4/1/2018	1300	4800
BBBB	1/1/2018	1400
BBBB	2/1/2018	1500	4200
BBBB	3/1/2018	1600	4500
BBBB	4/1/2018	1700	4800
CCCC	1/1/2018	1800
CCCC	2/1/2018	1900	4200
CCCC	3/1/2018	2000	4500
CCCC	4/1/2018	2100	4800

So for 2/1/2018, is adding up all 1/1/2018 Sales data, regardless of the product (1000+1400+1800)

I need for AAAA for 2/1/2018 the value of 1000, and so on....

What am I missing? Can you guide me?

[Hardik]

I think you need to calculate the sales on a column basis.You can sort the table data ccording to date and calculate if the month of the date is less than the month of the other date the sales should be of the previous month and so on

[jvalencia]

The Sales is a straight data. I indicated Sales as an example, but could be other value, such as age, or weight. My problem is that this is accumulating the value, not considering the different products.

[jvalencia]

In this Example, If a SITE is not selected, the previous month amount (COGS) is OK,

It is Showing the amount of february in March, and so on.....

However, when I select one SITE, the Previous month Column does not change to the selected Site, it remains the overall amount:

The Formulas I used are:

ALL COGS= CALCULATE ( SUM ( Data[COGS] ) )

COGS PM = calculate ( [ALL COGS] , PREVIOUSMONTH ( Table_Dates[Date] ) )

Should I used a Filter? how? Please guide me. Thanks!

[Ashish_Mathur]

Hi,

From which Table have you built the Site slicer?

[v-shex-msft]

HI @jvalencia,

Current power bi not support to use slicer or filter to create dynamic calculated column/table, please use measure to instead calculate column.

Regards,

Xiaoxin Sheng

[jvalencia]

Hello, they are Measures, not Calculated Columns.

The filter I was using it, in order to select another category and then I could have the PM to analize.