Calculate Previous month sales by data and by another column value

[pkilo]

Hello,

I need a measure, which calculate previous month value, so that I could easily calculate monthly % differences and comparison.

Previous Month value must be calculated by using both date and Operating system 

(I use both as report filters).

Table might look like this:

Date            OS          Quantity   

09.02.2017  Android   5

09.02.2017  IOS          2

10.02.2017  Android   4

10.02.2017  IOS          3

11.02.2017  Android   5

11.02.2017  IOS          2

12.02.2017  IOS          2

09.03.2017  Android   9

03.03.2017  IOS          3

09.03.2017  Android   5

09.03.2017  Android   9

I'm able to calculate total quantity from the previous month, but not the total quantity by each operating system from the previous month.

So if I filter Month = March 2017 and IOS     For previous month value I should receive = 9

Now I'm receiving the total February value for both OS = 23  //wrong

I had this same probler earlier, whit WoW comparsion, but the I got a greas solutions from this forum = to use Earlier (-7 days) function, but now the case is different. 

Thank you.

[CheenuSing · Accepted solution]

Hi @pkilo,

I am assuming you have a Calendar Table and it is linked to the Date of the transaction table posted.

1. Create a measure called SumQuantity = Sum([Quantity])

2. Create a measure called PrevMonthQuantity = Calculate ( sum([Quantity]), PREVIOUSMONTH(Calendar[Date]))

3. Create a Slicer for Month from Calendar Table

4. Create a Slicer for OS from transaction Tablle

5. Create a table visual using the Month from Calendar Table, OS from transaction table, measure SumQuantity, measure PrevMonthQuantity.

6. You will get the expected output as you want. Sample output with the data posted

If this solves your issue please accept it as a solution and also give KUDOS.

Cheers

CheenuSing

[pkilo]

Hey

Thank you for your solution reply @CheenuSing

It almost work.  It did not work for me with the PREVIOUSMONTH function, but it did work with DATEADD function.

So the formula for previous month sessions was:

PM Sessions = CALCULATE([Sum Sess];DATEADD('Calendar'[Date];-1;MONTH))

I never thought I shoud do the sum measure first.

-Päivi