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.
Hello.
There are a table "Sales" and a table "Date".
I want to do is the following.
1. Outputs Sales Amount of every month.
2. Calculate the month-over-month.
I referenced this site,but error came out.
■Site:
http://www.daxpatterns.com/time-patterns/#complete-aggregation-patterns
「Complete Period Comparison Patterns」
■Error Message:
A table of the value of the plural was designated, but the necessary value is only one.
How should it be done?
Please tell me.
Eriko.
Solved! Go to Solution.
Just tried something like this which gives me the previous month
Previous month = CALCULATE(SUM('GA Ecommerce'[Sales]),PREVIOUSMONTH('GA Ecommerce'[Date]))
or
Previous Month = CALCULATE(SUM('GA Ecommerce'[Sales]),DATEADD('GA Ecommerce'[Date],-1,MONTH))
Should be pretty easy to do MOM from this.
I'm facing an issue while calculating previous month sales for the following data:
TimeID | Date | FY | FYQuarter | Month |
1 | 4/1/2013 | 2013 | 1 | April |
2 | 4/1/2014 | 2014 | 1 | April |
3 | 4/1/2015 | 2015 | 1 | April |
4 | 8/1/2013 | 2013 | 2 | August |
5 | 8/1/2014 | 2014 | 2 | August |
6 | 8/1/2015 | 2015 | 2 | August |
7 | 12/1/2013 | 2013 | 3 | December |
8 | 12/1/2014 | 2014 | 3 | December |
9 | 12/1/2015 | 2015 | 3 | December |
10 | 2/1/2014 | 2013 | 4 | February |
11 | 2/1/2015 | 2014 | 4 | February |
12 | 2/1/2016 | 2015 | 4 | February |
13 | 1/1/2014 | 2013 | 4 | January |
14 | 1/1/2016 | 2015 | 4 | January |
15 | 7/1/2013 | 2013 | 2 | July |
16 | 7/1/2014 | 2014 | 2 | July |
17 | 7/1/2015 | 2015 | 2 | July |
I simply have the month and Year information. The day information was not available in th raw data and the 1st day in every date is added by me. This Date table stores the Fiscal year that begins in April.
I have calculated the revenue using Sales from a Fact Table. I now want to calculate the previous month's revenue. I used the DATEADD Function as shown below:
LastMonthRev = CALCULATE([Revenue],FILTER(DimTime,DATEADD(DimTime[Date],-1,MONTH)))
However I get the same value as this month's revenue(refer the table shown below)
Month | Revenue | LastMonthRev |
1 | $10.00 | $10 |
2 | $20.00 | $20 |
3 | $30.00 | $30 |
4 | $40.00 | |
5 | $50.00 | $50 |
6 | $60.00 | $60 |
7 | $70.00 | $70 |
8 | $80.00 | $80 |
9 | $90.00 | $90 |
10 | $100.00 | $100 |
11 | $110.00 | $110 |
12 | $120.00 | $120 |
This data is just for one year. Can someone help me in performing month over month calculations or just getting the last month's revenue correctly?
Thanks a lot @disha_khannayou spare me so much time! thel Filter help to what i was missing! but still have some issues with the MoM comparison.
did anybody get the solution for this
i am using PrevMonthRev = CALCULATE(sum(salesdata[revenue]),PREVIOUSMONTH('date'[Month])) but it is not giving any data
Thank you for suggesting this, I made a change that worked for calculating the day over day using this methodology:
NON-WORKING EXAMPLE:
WORKING EXAMPLE:
date table
DateKey | Year | Month |
1 | 2015 | 1-Jan |
2 | 2015 | 1-Feb |
3 | 2015 | 1-Mar |
4 | 2015 | 1-Apr |
5 | 2015 | 1-May |
6 | 2015 | 1-Jun |
7 | 2015 | 1-Jul |
8 | 2015 | 1-Aug |
9 | 2015 | 1-Sep |
10 | 2015 | 1-Oct |
11 | 2015 | 1-Nov |
12 | 2015 | 1-Dec |
13 | 2016 | 1-Jan |
14 | 2016 | 1-Feb |
15 | 2016 | 1-Mar |
16 | 2016 | 1-Apr |
17 | 2016 | 1-May |
18 | 2016 | 1-Jun |
19 | 2016 | 1-Jul |
20 | 2016 | 1-Aug |
sales table
DateKey | revenue |
1 | 123 |
2 | 234 |
3 | 345 |
4 | 456 |
5 | 567 |
6 | 678 |
7 | 789 |
8 | 8910 |
9 | 91011 |
10 | 101112 |
11 | 111213 |
12 | 121314 |
13 | 231 |
14 | 248 |
15 | 391 |
16 | 487 |
17 | 598 |
18 | 694 |
19 | 799 |
20 | 8980 |
Can you post some sample data and your formula. From your error, it looks like one of your functions is returning a table when a single column is expected, or something along those lines.
Just tried something like this which gives me the previous month
Previous month = CALCULATE(SUM('GA Ecommerce'[Sales]),PREVIOUSMONTH('GA Ecommerce'[Date]))
or
Previous Month = CALCULATE(SUM('GA Ecommerce'[Sales]),DATEADD('GA Ecommerce'[Date],-1,MONTH))
Should be pretty easy to do MOM from this.
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 |
---|---|
107 | |
97 | |
75 | |
63 | |
53 |
User | Count |
---|---|
139 | |
100 | |
95 | |
85 | |
63 |