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.
I am triying to calculate sale amount difference between season of context and previous season.
But I don't know how to solve this problem.
Data is below:
Fact Table
SaleMonth | Season | SaleAmount |
2018-06-01 | U | 604 |
2018-04-01 | U | 10 |
2018-10-01 | U | 322 |
2017-10-01 | U | 557 |
2016-03-02 | U | 12 |
2018-05-01 | Q | 49 |
2018-06-01 | Q | 948 |
2017-04-01 | Q | 134 |
2017-05-01 | Q | 427 |
2016-12-01 | Q | 578 |
2018-06-01 | R | 810 |
2018-06-01 | R | 431 |
2018-06-01 | R | 167 |
2017-12-01 | R | 935 |
2017-11-01 | R | 777 |
2018-08-01 | W | 447 |
2018-08-01 | W | 799 |
2018-07-01 | W | 133 |
Dimension Table
Season | SeasonYear | Categroy |
Q | 2017 | S/S |
R | 2017 | F/W |
U | 2018 | S/S |
W | 2018 | F/W |
I have to call sale amounts about previous season on current context.
I don't call values which are on difference rows.
Solved! Go to Solution.
Hi @Anonymous
Create relationship as below
Create measures
previous = CALCULATE(SUM(Sheet1[SaleAmount]),FILTER(ALLEXCEPT(Sheet2,Sheet2[Categroy]),[SeasonYear]=MAX([SeasonYear])-1)) diff = SUM(Sheet1[SaleAmount])-[previous]
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous
Create relationship as below
Create measures
previous = CALCULATE(SUM(Sheet1[SaleAmount]),FILTER(ALLEXCEPT(Sheet2,Sheet2[Categroy]),[SeasonYear]=MAX([SeasonYear])-1)) diff = SUM(Sheet1[SaleAmount])-[previous]
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Actually I want to filter data by season only, not year.
But this solution is good as i filter the [Category].
Thank you very much!
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 |
---|---|
97 | |
97 | |
82 | |
75 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |