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.
To start, I received a deadline for Friday and have been breaking my head over this for the past few weeks.
My problem is dat previousyear works as you expect. Within its own context it makes a calculation of this. So if you have sold pieces in region 1, 2 and 3 from this year and past year and you stuff the measure in a matrix where on row level is the year and regions, it calculates it without any problem.
Now the problem starts. There is an additional filter to show whether current year sales is actually 'maintained'. Maintained is calculated whether the product was sold previous year based on a monthy YTD calculation. This verification is put in the raw data table where the measures are running on. The problem is that now 'new' sales with a calculation of previousyear do have sales. This since it makes the calculations of previous year on the context of the filter in the raw data table. If 2019 had an X amount of sales on 'new' products, the last year sales measure for 2020 on 'new' products, will sum to the amount of X in 2019. However, these X sales is rather for 2020 considered as 'maintained' sales or lost sales, but never 'new'.
I was hoping someone could tell me. I've trying to redesign my tables, work with different relationships but all to no avail.
Original measure:
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'.
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 |
---|---|
112 | |
99 | |
73 | |
72 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |