Hello Everyone!
I am connecting to Azure Table Storage and showing multiple attributes and Values by week. The weeks are formatted as w192001, w192002 with 1920 as fiscal year and last 2 digits are week number. In the attached file, I am showing Metrics Category and SubCategory. Actual data is at far lower grain than Metric-Week combination but I guess that doesn't matter as I am already covering few here.
What is needed - When I refresh the report each week, all other Metrics are fine but for Defected, I should make value for all previous weeks as 0 while for others it should be whatever is coming from table. I cannot use calculated table as the table contains around 50M rows and data is increasing every week.
The date table can be created in a different way if needed.
Please suggest how to create a measure for this calculation:-
IF WeekinData < CurrentWeek AND Metric = Defected, then Value = 0 ELSE Value = Value. ( Complexity here is to first calculate current week from date table and then compare it with week and metric in data table. I am also trying to avoid calculating another column to conver week in weeknbr like w192001 to just 192001, so that should be taken care within the measure).
**In actual report, I have around 200 metrics and this calculation is needed for 5.
Sample Data
| WeekID | Metric | Category | Sub-Category | Value |
| w192001 | Sold Units | Electronics | Laptops | 2100 |
| w192001 | Defected | Electronics | Laptops | 10 |
| w192001 | Ordered | Electronics | Laptops | 1500 |
| w192002 | Sold Units | Electronics | Laptops | 2500 |
| w192002 | Defected | Electronics | Laptops | 2 |
| w192002 | Ordered | Electronics | Laptops | 1800 |
| w192003 | Sold Units | Electronics | Laptops | 2200 |
| w192003 | Defected | Electronics | Laptops | 15 |
| w192003 | Ordered | Electronics | Laptops | 1000 |
| Date | WeekID |
| 20/07/2020 | w192001 |
| 21/07/2020 | w192001 |
| 22/07/2020 | w192001 |
| 23/07/2020 | w192001 |
| 24/07/2020 | w192001 |
| 25/07/2020 | w192001 |
| 26/07/2020 | w192001 |
| 27/07/2020 | w192002 |
| 28/07/2020 | w192002 |
| 29/07/2020 | w192002 |
| 30/07/2020 | w192002 |
| 31/07/2020 | w192002 |
| 01/08/2020 | w192002 |
| 02/08/2020 | w192002 |
| 03/08/2020 | w192003 |
| 04/08/2020 | w192003 |
| 05/08/2020 | w192003 |
| 06/08/2020 | w192003 |
| 07/08/2020 | w192003 |
| 08/08/2020 | w192003 |
| 09/08/2020 | w192003 |
Expected Result:-
| | WeekID |
| Metric | w192001 | w192002 | w192003 |
| Sold Units | 2100 | 2500 | 2200 |
| Defected | 0 | 0 | 15 |
| Ordered | 1500 | 1800 | 1000 |
Thank You!
