Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
Ledger | Date | Amount |
19 Actual | 1/02/2019 | 54 |
19 Actual | 3/02/2019 | 99 |
19 Actual | 10/02/2019 | 89 |
19 Actual | 1/03/2019 | 79 |
19 Actual | 9/03/2019 | 50 |
19 Actual | 15/03/2019 | 52 |
19 Actual | 2/04/2019 | 95 |
19 Actual | 20/04/2019 | 81 |
19 Actual | 25/04/2019 | 92 |
19 Budget | 1/01/2019 | 98 |
19 Budget | 1/01/2019 | 90 |
19 Budget | 1/01/2019 | 74 |
19 Budget | 1/02/2019 | 50 |
19 Budget | 1/02/2019 | 63 |
19 Budget | 1/02/2019 | 66 |
19 Budget | 1/03/2019 | 84 |
19 Budget | 1/03/2019 | 68 |
19 Budget | 1/04/2019 | 73 |
19 Forecast | 1/02/2019 | 64 |
19 Forecast | 1/02/2019 | 73 |
19 Forecast | 1/02/2019 | 90 |
19 Forecast | 1/03/2019 | 50 |
19 Forecast | 1/03/2019 | 83 |
19 Forecast | 1/03/2019 | 78 |
19 Forecast | 1/04/2019 | 63 |
19 Forecast | 1/04/2019 | 58 |
19 Forecast | 1/04/2019 | 90 |
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 Date | Jan | Feb | Mar | Apr | May |
19 Actual | 0 | 242 | 423 | 691 | 691 |
19 Budget | 0 | 441 | 593 | 666 | 666 |
19 Forecast | 0 | 227 | 438 | 649 | 649 |
% Actual Complete | 0.00% | 54.88% | 71.33% | 103.75% | 103.75% |
% Forecast Complete | 0.00% | 51.47% | 73.86% | 97.45% | 97.45% |
Thanks in advance for your help.
V.
Solved! Go to 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
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
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
User | Count |
---|---|
41 | |
27 | |
23 | |
19 | |
16 |
User | Count |
---|---|
54 | |
35 | |
19 | |
19 | |
15 |