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.
Hi All,
I am new to power BI. I'm trying to calculate ratio of this year quarter sales to previous year quarter sales. Ie. Sum of Annual revenue in Q3 of 2016 divided by Sum of Annual revenue in Q3 of 2015 for a particular Shop Name.
I have tried using DAX functionality, tried summarizing the tables but not successful. Please see sample data below:
Date | Quarter | Annual revenue | Volume | Latitude | Longitude | City | Shop Name | Distributor |
01-01-2016 | Q1-16 | 1246 | 34.61 | 19.115836 | 72.881799 | Mumbai | a | Abhinav |
01-02-2016 | Q1-16 | 1368 | 38.00 | 19.115836 | 72.881799 | Mumbai | a | Abhinav |
01-03-2016 | Q1-16 | 1879 | 52.19 | 19.115836 | 72.881799 | Mumbai | a | Abhinav |
01-04-2016 | Q2-16 | 1065 | 29.58 | 19.115836 | 72.881799 | Mumbai | a | Abhinav |
01-05-2016 | Q2-16 | 1771 | 49.19 | 19.115836 | 72.881799 | Mumbai | a | Abhinav |
01-06-2016 | Q2-16 | 1906 | 52.94 | 19.115836 | 72.881799 | Mumbai | a | Abhinav |
01-07-2016 | Q3-16 | 1430 | 39.72 | 19.115836 | 72.881799 | Mumbai | a | Abhinav |
01-08-2016 | Q3-16 | 1624 | 45.11 | 19.115836 | 72.881799 | Mumbai | a | Abhinav |
01-09-2016 | Q3-16 | 1509 | 41.92 | 19.115836 | 72.881799 | Mumbai | a | Abhinav |
01-10-2016 | Q4-16 | 1699 | 47.19 | 19.115836 | 72.881799 | Mumbai | a | Abhinav |
01-11-2016 | Q4-16 | 1825 | 50.69 | 19.115836 | 72.881799 | Mumbai | a | Abhinav |
01-12-2016 | Q4-16 | 1696 | 47.11 | 19.115836 | 72.881799 | Mumbai | a | Abhinav |
01-01-2016 | Q1-16 | 1238 | 34.39 | 19.109918 | 72.906136 | Mumbai | b | Abhinav |
01-02-2016 | Q1-16 | 1525 | 42.36 | 19.109918 | 72.906136 | Mumbai | b | Abhinav |
01-03-2016 | Q1-16 | 1910 | 53.06 | 19.109918 | 72.906136 | Mumbai | b | Abhinav |
01-04-2016 | Q2-16 | 1263 | 35.08 | 19.109918 | 72.906136 | Mumbai | b | Abhinav |
01-05-2016 | Q2-16 | 1846 | 51.28 | 19.109918 | 72.906136 | Mumbai | b | Abhinav |
01-06-2016 | Q2-16 | 1856 | 51.56 | 19.109918 | 72.906136 | Mumbai | b | Abhinav |
01-07-2016 | Q3-16 | 1045 | 29.03 | 19.109918 | 72.906136 | Mumbai | b | Abhinav |
01-08-2016 | Q3-16 | 1518 | 42.17 | 19.109918 | 72.906136 | Mumbai | b | Abhinav |
01-09-2016 | Q3-16 | 1229 | 34.14 | 19.109918 | 72.906136 | Mumbai | b | Abhinav |
01-10-2016 | Q4-16 | 1600 | 44.44 | 19.109918 | 72.906136 | Mumbai | b | Abhinav |
01-11-2016 | Q4-16 | 1547 | 42.97 | 19.109918 | 72.906136 | Mumbai | b | Abhinav |
01-12-2016 | Q4-16 | 1165 | 32.36 | 19.109918 | 72.906136 | Mumbai | b | Abhinav |
01-01-2016 | Q1-16 | 1422 | 39.50 | 19.089966 | 72.896866 | Mumbai | c | Abhinav |
01-02-2016 | Q1-16 | 1305 | 36.25 | 19.089966 | 72.896866 | Mumbai | c | Abhinav |
01-03-2016 | Q1-16 | 1888 | 52.44 | 19.089966 | 72.896866 | Mumbai | c | Abhinav |
01-04-2016 | Q2-16 | 1926 | 53.50 | 19.089966 | 72.896866 | Mumbai | c | Abhinav |
01-05-2016 | Q2-16 | 1721 | 47.81 | 19.089966 | 72.896866 | Mumbai | c | Abhinav |
01-06-2016 | Q2-16 | 1447 | 40.19 | 19.089966 | 72.896866 | Mumbai | c | Abhinav |
01-07-2016 | Q3-16 | 1930 | 53.61 | 19.089966 | 72.896866 | Mumbai | c | Abhinav |
01-08-2016 | Q3-16 | 1667 | 46.31 | 19.089966 | 72.896866 | Mumbai | c | Abhinav |
01-09-2016 | Q3-16 | 1436 | 39.89 | 19.089966 | 72.896866 | Mumbai | c | Abhinav |
01-10-2016 | Q4-16 | 1030 | 28.61 | 19.089966 | 72.896866 | Mumbai | c | Abhinav |
01-11-2016 | Q4-16 | 1784 | 49.56 | 19.089966 | 72.896866 | Mumbai | c | Abhinav |
01-12-2016 | Q4-16 | 1333 | 37.03 | 19.089966 | 72.896866 | Mumbai | c | Abhinav |
01-01-2016 | Q1-16 | 1989 | 55.25 | 19.06628 | 72.850775 | Mumbai | d | Abhinav |
01-02-2016 | Q1-16 | 1983 | 55.08 | 19.06628 | 72.850775 | Mumbai | d | Abhinav |
01-03-2016 | Q1-16 | 1285 | 35.69 | 19.06628 | 72.850775 | Mumbai | d | Abhinav |
01-04-2016 | Q2-16 | 1743 | 48.42 | 19.06628 | 72.850775 | Mumbai | d | Abhinav |
01-05-2016 | Q2-16 | 1207 | 33.53 | 19.06628 | 72.850775 | Mumbai | d | Abhinav |
01-06-2016 | Q2-16 | 1376 | 38.22 | 19.06628 | 72.850775 | Mumbai | d | Abhinav |
01-07-2016 | Q3-16 | 1221 | 33.92 | 19.06628 | 72.850775 | Mumbai | d | Abhinav |
01-08-2016 | Q3-16 | 1960 | 54.44 | 19.06628 | 72.850775 | Mumbai | d | Abhinav |
01-09-2016 | Q3-16 | 1910 | 53.06 | 19.06628 | 72.850775 | Mumbai | d | Abhinav |
01-10-2016 | Q4-16 | 1564 | 43.44 | 19.06628 | 72.850775 | Mumbai | d | Abhinav |
01-11-2016 | Q4-16 | 1452 | 40.33 | 19.06628 | 72.850775 | Mumbai | d | Abhinav |
01-12-2016 | Q4-16 | 1486 | 41.28 | 19.06628 | 72.850775 | Mumbai | d | Abhinav |
I think this is possible in PowerBI but somehow I am unable to get the results.
Any pointers or help will be much appricated.
Regards,
Surya Ostwal
Solved! Go to Solution.
Hi @surya2193,
In your scenario, you can create a calendar table, build a relationship between fact table and this calendar table based on Date field. Then create a measure to return total revenue in last year same period use SAMEPERIODLASTYEAR Function (DAX) , then use like below:
LastYearSameQuarter=CALCULATE(SUM('Fact'[Annual revenue]),SAMEPERIODLASTYEAR('Date'[DATEKEY]))
ThisYearQuarter/LastYearSameQuarter = DIVIDE(SUM('Fact'[Annual revenue]),CALCULATE(SUM('Fact'[Annual revenue]),SAMEPERIODLASTYEAR('Date'[DATEKEY])),0)
Best Regards,
Qiuyun Yu
Hi @surya2193,
In your scenario, you can create a calendar table, build a relationship between fact table and this calendar table based on Date field. Then create a measure to return total revenue in last year same period use SAMEPERIODLASTYEAR Function (DAX) , then use like below:
LastYearSameQuarter=CALCULATE(SUM('Fact'[Annual revenue]),SAMEPERIODLASTYEAR('Date'[DATEKEY]))
ThisYearQuarter/LastYearSameQuarter = DIVIDE(SUM('Fact'[Annual revenue]),CALCULATE(SUM('Fact'[Annual revenue]),SAMEPERIODLASTYEAR('Date'[DATEKEY])),0)
Best Regards,
Qiuyun Yu
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 |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |