Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Calculation - % Complete YTD

Hi,

 

I need some help to create a measure that gives me a YTD % by month. I've shown the data as I would do it in Excel, but I'm not sure what I need to include in my measure in Power BI. 

 

I have a data set as follows:

 

LedgerDateAmount
19 Actual1/02/201954
19 Actual3/02/201999
19 Actual10/02/201989
19 Actual1/03/201979
19 Actual9/03/201950
19 Actual15/03/201952
19 Actual2/04/201995
19 Actual20/04/201981
19 Actual25/04/201992
19 Budget1/01/201998
19 Budget1/01/201990
19 Budget1/01/201974
19 Budget1/02/201950
19 Budget1/02/201963
19 Budget1/02/201966
19 Budget1/03/201984
19 Budget1/03/201968
19 Budget1/04/201973
19 Forecast1/02/201964
19 Forecast1/02/201973
19 Forecast1/02/201990
19 Forecast1/03/201950
19 Forecast1/03/201983
19 Forecast1/03/201978
19 Forecast1/04/201963
19 Forecast1/04/201958
19 Forecast1/04/201990

 

In excel I would create a table for the amount YTD, and then do a percentage with the actual or forecast as the numerator, and the budget as the denominator. 

 

Year to DateJanFebMarAprMay
19 Actual0242423691691
19 Budget0441593666666
19 Forecast0227438649649
      
      
% Actual Complete0.00%54.88%71.33%103.75%103.75%
% Forecast Complete0.00%51.47%73.86%97.45%97.45%

 

Thanks in advance for your help. 

 

V. 

 

1 ACCEPTED SOLUTION

Hi @Anonymous ,

 

That's possible but you will need to use the CALCULATE function, and this will result in much more measures. And be aware of the filter context in this case.

 

Regards,

Mauricio

View solution in original post

3 REPLIES 3
MauriceMecowe
Resolver II
Resolver II

 

Hi Verity,

 

You can either split your excel sheet in a proper star model, so a table for Actual, Budget, Forecast and one for Date. This is what I prefer. From there it's very simple to continue and reach your goal. Create measures to sum the revenue for each table (Actual, Budget and Forecast). To get the percentage you can divide the measures to get the correct percentage. If you then add the Month from the Date dimension table, it will automatically slice your figure by month.

 

 

Actual = SUM(Actual[Revenue])

Actual Complete = (Actual[Actual]/Budget[Budget])
Budget = SUM(Budget[Revenue])

Hope this helps,

Maurice

 

Result.JPGStar Model.JPG

 

 

Anonymous
Not applicable

Thanks for the quick response. Is there a solution that would let me keep the data as I have it in one sheet? I have other calculations running off that sheet that I would prefer not to duplicate. 

Hi @Anonymous ,

 

That's possible but you will need to use the CALCULATE function, and this will result in much more measures. And be aware of the filter context in this case.

 

Regards,

Mauricio

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors