Calculating Quantity Sold last month & two months ago?

[Anonymous]

Hello, 

I am trying to calculate total quantity sold the last month and two months ago, but it is giving me wrong quantity. Can anybody please tell me whats wrong or take a look at my sample file. I really appreciate your help. 

Formula - 

One Month Ago = CALCULATE([Total Sold Quantity], DATEADD('Date'[Day], -1,MONTH))
Two Month Ago = CALCULATE([Total Sold Quantity], DATEADD(TestDataForSales[Day], -2,MONTH))

Click Here For Sample File 

Thank you so much 

[v-danhe-msft · Accepted solution]

Hi @Anonymous,

Based on my test, you can refer to below steps:

1.Create two measures to create your “one month ago data” and the “two month ago data”

New One month ago = CALCULATE (

                SUM(TestDataForSales[Quantity]),

                PARALLELPERIOD('TestDataForSales'[Day],-1,MONTH))-

    CALCULATE (

                SUM(TestDataForSales[Quantity]),

                PARALLELPERIOD('TestDataForSales'[Day],-2,MONTH))

New Two month ago = CALCULATE (

                SUM(TestDataForSales[Quantity]),

                PARALLELPERIOD('TestDataForSales'[Day],-2,MONTH))-

    CALCULATE (

                SUM(TestDataForSales[Quantity]),

                PARALLELPERIOD('TestDataForSales'[Day],-3,MONTH))

2.Create a Table visual and add the [Products], [SKU], [New One month ago] and [New Two month ago] fields.

3.Create two card visuals and add the [New One month ago] and [New Two month ago] fields.

Now you can see the result.

You can also download the PBIX file to have a view.

https://www.dropbox.com/s/q4l1cjnor65nzgp/Calculating%20Quantity%20Sold%20last%20month%20%26%20two%20months%20ago.pbix?dl=0

Regards,

Daniel He

[Richard146]

This solution worked for me as well but it is very resource intensive on large sets of data. It takes ages to refresh the dataset when this measure is added to a visual. 

[Anonymous]

@v-danhe-msft

Hello, 

This works and it makes sense.  Thank you so much for the time and help. 

[Anonymous]

i dont see any issues with your formula .

I hope your total sold qty is SUM( QTY) to make your calculation as shown below.

CALCULATE(SUM(Sales.Quanity_sold),DATEADD(Dates[Date],-1,MONTH)

Due to my access issues in laptop, am unable to download your file. Please make sure your measures are aggregating properly and relationship are set properly.

Thanks

Raj

[Anonymous]

@Anonymous

Hello, 

Thank you for the help and time.  We have to minus months.