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,
I have store sales data by reporting week, but not each reporting week has data for each store. What I want to do is create a new table by reporting week that sums the sales data of the latest entry of each store in order to create a line graph by reporting week.
ROW | STORE_KEY | Reporting Week (a) | Value | This entry rank by store |
1 | A | 201801 | 342.99 | 1 |
2 | A | 201802 | 2,608.26 | 2 |
3 | B | 201802 | 2,771.60 | 1 |
4 | B | 201804 | 10,613.88 | 2 |
5 | A | 201805 | 852.88 | 3
|
Reporting Week (b) | Value | Calculation explantion 1 | Calculation explantion 2 | Calculation explantion 3 |
201801 | 342.99 | Row 1 | 342.99 + null | only store 2497 has entry before or equal to week 201801 |
201802 | 5,379.86 | Row 2 + Row 3 | 2,608.26 + 2,771.60 | Row 1 data from 201801 is replaced by Row 2 data from 201802 |
201803 | 5379.86 | Row 2 + Row 3 | 2,608.26 + 2,771.60 | No new data from 201803 so latest data from 201802 is used |
201804 | 13,222.14 | Row 2 + row 4 | 2,608.26 + 10,613.88 | , Store A's latest data is from 201802 so this is used, Store B has data from 201804 which is used |
201805 | 11,466.76 | Row 4 + Row 5 | 10,613.88 + 852.88 | Store A has data from 201805 so this is used, Store B's latest data is still from 201804 |
I've been succesfull in summing the sales value if Reporting week (a) from table 1 is LT or EQ to Reporting week (b) from my calculated table but this sums all values whereas I only want to sum the latest value for each STORE KEY. I've tried using measures using MAX functions of the entry rank but have been unsuccesful. help please!
Solved! Go to Solution.
I reposted this elsewhere because this topic was hidden for the first few days after posting as it was flagged for spam.
Solution is here:
Hi @Anonymous ,
I think your problem is not easy to calculate by DAX, I think it's more easier to solve in EXCEL, then, you can import the data you calculated.
Best regards
Lionel Chen
I reposted this elsewhere because this topic was hidden for the first few days after posting as it was flagged for spam.
Solution is here:
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 |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |