| Fake Dataset | | | | | | | | | |
| *Users should be able to filter via slicers on data source type, program, province, TXN ID, TXN Coverage, And Calendar Year |
| Data Source Type | Program | Province | Txn ID | TXN Coverage | Calendar Period | Calendar Year | Value_R | Value_IL | |
| A | AAAA | ON | C1 | BI | 9/30/2018 | 2018 | 1366 | 205 | |
| A | AAAA | ON | C1 | BI | 6/30/2019 | 2019 | -1350 | 3606 | |
| A | AAAA | ON | C1 | AB | 7/31/2019 | 2019 | -1350 | 4844 | |
| A | EEEE | ON | C2 | BI | 11/30/2020 | 2020 | -1000 | 2926 | |
| A | EEEE | ON | C2 | AB | 12/31/2020 | 2020 | -1000 | 4307 | |
| A | EEEE | ON | C2 | BI | 1/31/2022 | 2022 | 1667 | 3521 | *note it skipped 2021 here to mimick the real data |
| A | EEEE | ON | C2 | AB | 1/31/2022 | 2022 | 1667 | 1933 | |
| B | BBBB | BC | C3 | BI | 4/30/2018 | 2018 | -2438 | 4590 | |
| B | BBBB | BC | C3 | BI | 6/30/2019 | 2019 | -2500 | 4180 | |
| B | BBBB | BC | C3 | BI | 8/31/2020 | 2020 | -2500 | 2607 | |
| B | BBBB | BC | C3 | BI | 1/31/2021 | 2021 | 7437 | 1436 | |
| B | BBBB | BC | C3 | BI | 8/31/2022 | 2022 | 1 | 2432 | |
| C | CCCC | QC | C4 | AB | 1/31/2018 | 2018 | 3752 | 4828 | |
| C | CCCC | QC | C4 | AB | 7/31/2019 | 2019 | 1461 | 4826 | |
| C | CCCC | QC | C4 | AB | 3/31/2020 | 2020 | 4891 | 4183 | |
| C | CCCC | QC | C4 | AB | 7/31/2021 | 2021 | -9314 | 1096 | |
| C | CCCC | QC | C4 | AB | 11/30/2022 | 2022 | 940 | 799 | |
| D | DDDD | AB | C5 | AB | 6/30/2018 | 2018 | 2836 | 4579 | |
| D | DDDD | AB | C5 | AB | 2/28/2019 | 2019 | -6581 | 1330 | |
| D | DDDD | AB | C5 | AB | 4/30/2020 | 2020 | 930 | 3890 | |
| D | DDDD | AB | C5 | AB | 4/30/2021 | 2021 | -6667 | 3321 | |
| D | DDDD | AB | C5 | AB | 9/30/2022 | 2022 | 3610 | 482 | |
| 1. Make a new table in PBI called Calendar Period, ranging from 2018/1/1 ~ 2022/12/31. |
| This is so if the user selects on data source system = A, the Calendar_Year Slicer still shows 2021. |
| Connect to the Fake Dataset by Calendar_Period | | |
| Calendar Period | Calendar Year | Calendar Month | | | and so on |
| 1/31/2018 | 2018 | 1 | | | | |
| 2/28/2018 | 2018 | 2 | | | | |
| 2. Suppose the user selects datasource = A & Calendar Year = 2021 | | |
| For each Txn ID, find the cumulative value_R up to the previous year, aka 2020, and round it to 2 digits, and apply abs() |
| | | | | | | | |
| Data Source Type | Program | Province | Txn ID | TXN Coverage | Calendar Period | Calendar Year | Value_R |
| A | AAAA | ON | C1 | BI | 9/30/2018 | 2018 | 1366 |
| A | AAAA | ON | C1 | BI | 6/30/2019 | 2019 | -1350 |
| A | AAAA | ON | C1 | AB | 7/31/2019 | 2019 | -1350 |
| A | EEEE | ON | C2 | BI | 11/30/2020 | 2020 | -1000 |
| A | EEEE | ON | C2 | AB | 12/31/2020 | 2020 | -1000 |
| | | | | | | | |
| Txn ID | Cumulative Value_R | | | | |
| C1 | 1334 | | | | | | |
| C2 | 2000 | | | | | | |
| 3. If The cumulative Value_R > 0, then find the cumulative value_IL up to the previous year | |
| | | | | | | | | |
| | | | | | | | | |
| Data Source Type | Program | Province | Txn ID | TXN Coverage | Calendar Period | Calendar Year | Value_R | Value_IL |
| A | AAAA | ON | C1 | BI | 9/30/2018 | 2018 | 1366 | 205 |
| A | AAAA | ON | C1 | BI | 6/30/2019 | 2019 | -1350 | 3606 |
| A | AAAA | ON | C1 | AB | 7/31/2019 | 2019 | -1350 | 4844 |
| A | EEEE | ON | C2 | BI | 11/30/2020 | 2020 | -1000 | 2926 |
| A | EEEE | ON | C2 | AB | 12/31/2020 | 2020 | -1000 | 4307 |
| | | | | | | | | |
| Txn ID | Cumulative Value_IL | | | | | | |
| C1 | 8655 | | | | | | | |
| C2 | 7233 | | | | | | | |
| 4. Desired Table | | | | |
| | | | | |
| | | | | |
| Data Source Type | Program | Province | Txn ID | Cumulative Value_IL |
| A | AAAA | ON | C1 | 8655 |
| A | EEEE | ON | C2 | 7233 |
| Total | | | | 15888 |
| | | | | |
| *Problem: Everything is fine until the Total; it has something do with using IF statement on my end… need a revision to show the sum correctly (the row values are fine) |
The column names my DAX uses may slightly be different.
Thanks!
