Forum Discussion
Previousyear excluding a filter
Can you share sample data and sample output.
Sort of, can't share you a pbix due to restriction.
Test table:
| Year | Product | Sales | Business |
| 2018 | A | 5 | New |
| 2018 | A | 3 | New |
| 2018 | B | 8 | New |
| 2018 | A | 17 | New |
| 2018 | A | 21 | New |
| 2019 | A | 12 | Maintain |
| 2019 | A | 15 | Maintain |
| 2019 | B | 2 | Maintain |
| 2019 | C | 8 | New |
| 2020 | A | 5 | Maintain |
| 2020 | B | 3 | Maintain |
| 2020 | C | 18 | Maintain |
| 2020 | D | 20 | New |
Ass you can see, for 2019 there is maintained sales, however, for 2018 there wasn't any 'maintained' sales, only 'new' sales. Both products A and B had been sold in 2018 and thus should populate the number of Sales LY for maintained business in 2019.
DouweMeer ,Please check: https://www.dropbox.com/s/vighl8efolwisj4/previousYear_yearand%20date.pbix?dl=0
I converted to date and used time intelligence
Was one of my attempts as well. What you get is due to the 1 to many relationship one way that products duplicate in a year. Once as a maintain and once as 'new'. The problem lies in the previousyear once again.
A is an example, B has this for 2019 as well. C has it in 2020 as it is the first year it is 'maintained'.