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.
Need help with a calculation on variance for sales target.
I have 1 table with the Sales information with Sales recorded by the Teams and the second with Traget qty by teams.
Date | Order | Team | Qty | Group | Customer |
1-Jun-20 | 1000 | Team A | 20 | GRP1 | 100 |
1-Jul-20 | 1001 | Team A | 50 | GRP1 | 101 |
1-Aug-20 | 1002 | Team B | 100 | GRP2 | 102 |
1-May-21 | 2000 | Team A | 125 | GRP2 | 103 |
1-May-21 | 2001 | Team A | 100 | GRP1 | 101 |
1-Nov-20 | 1001 | Team A | 250 | GRP1 | 101 |
1-Nov-21 | 1001 | Team A | 400 | GRP1 | 101 |
Second table have the target for the Fiscal Year by Teams
FY | Team | Target Qty | Group | Customer |
FY21 | Team A | 2000 | GRP1 | 100 |
FY22 | Team A | 3500 | GRP2 | 101 |
FY21 | Team B | 1800 | GRP3 | 102 |
FY22 | Team B | 3000 | GRP4 | 103 |
I created a key to look up the Target qty from the Target info for the fact. But my issue is with the variance calculation.
Filter is for the Fiscal Year Month. FY ends 04/30.
How should i do measure on variance?
For the selected fiscal year Month of Nov 2021,
Expected result is to
Team | Last Year | Target | Variance | Cur Year MTD |
Team A | 320 | 2000 | -1680 | 400 |
@Pbi07 , In year table, create a year-end date date based on FY
example
Date = Date(right([FY],2), 3, 31) // Use + with FY wif needed Date(right([FY],2) +1 , 3, 31)
then refer
Distributing/Allocating the Yearly Target(Convert to Daily Target): Measure ( Daily/YTD): Magic of CLOSINGBALANCEYEAR With TOTALYTD/DATESYTD: https://community.powerbi.com/t5/Community-Blog/Power-BI-Distributing-Allocating-the-Yearly-Target-C...
Distributing/Allocating the Monthly Target(Convert to Daily Target): Measure ( Daily/MTD): https://community.powerbi.com/t5/Community-Blog/Power-BI-Distributing-Allocating-the-Monthly-Target-...
Thanks @amitchandak . My request is totally different. Sorry if i did not made it clear.
I am trying to get the calculation as below
Varaince from Target = Target from Target Table - Total Sales Measure
Noticed that i mistyped the Target in the expected result.
Team | Last Year | Target | Variance | Cur Year MTD |
Team A | 320 | 3500 | -3180 | 400 |
Pbix is here - https://drive.google.com/file/d/19UqWP2xwWK5eB4S2CUngc5abiWCHHIDG/view?usp=sharing
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 |
---|---|
114 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |