cancel
Showing results for
Did you mean:
Helper IV

## Calculating Variance based on a Target

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

2 REPLIES 2
Super User

@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-...

Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!
Helper IV

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

Announcements

#### The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

#### Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!