cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Pbi07
Helper IV
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. 

 

DateOrderTeamQtyGroupCustomer
1-Jun-201000Team A20GRP1100
1-Jul-201001Team A50GRP1101
1-Aug-201002Team B100GRP2102
1-May-212000Team A125GRP2103
1-May-212001Team A100GRP1101
1-Nov-201001Team A250GRP1101
1-Nov-211001Team A400GRP1101

 

Second table have the target for the Fiscal Year by Teams

FYTeamTarget QtyGroupCustomer
FY21Team A2000GRP1100
FY22Team A3500GRP2101
FY21Team B1800GRP3102
FY22Team B3000GRP4103

 

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. 

 

Last Year Sale = CALCULATE(SUM(Fact[Qty]),DATESYTD(ENDOFYEAR(dateadd('calendar'[Date],-1,Year),"04/30"),"04/30"))

How should i do measure on variance? 

For the selected fiscal year Month of Nov 2021, 

Expected result is to 

TeamLast YearTargetVarianceCur Year MTD
Team A3202000-1680400
     

 

2 REPLIES 2
amitchandak
Super User
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 !!

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. 

 

 

 

TeamLast YearTargetVarianceCur Year MTD
Team A3203500-3180400
     

 

 

Pbix is here - https://drive.google.com/file/d/19UqWP2xwWK5eB4S2CUngc5abiWCHHIDG/view?usp=sharing

 

Helpful resources

Announcements
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

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

Power BI Dev Camp Session 24 without aka link and time 768x460.jpg

Ted's Dev Camp - July 28, 2022

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

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors