Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
One Month Ago = CALCULATE([Total Sold Quantity], DATEADD('Date'[Day], -1,MONTH)) Two Month Ago = CALCULATE([Total Sold Quantity], DATEADD(TestDataForSales[Day], -2,MONTH))
Solved! Go to 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.
Regards,
Daniel He
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.
Regards,
Daniel He
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.
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
Hello,
Thank you for the help and time. We have to minus months.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
100 | |
78 | |
76 | |
52 |
User | Count |
---|---|
144 | |
109 | |
108 | |
88 | |
61 |