Forum Discussion
Time Intelligence Function - Do I need a calendar?
Hi, I am a bit confused if I would require a calendar for my data analysis.
I viewed a post similar to my question but it did not give the answer I needed. My data is extracted from SAP to MS Excel as the example shown below. I am trying to perform an analysis of my current year actuals vs current year budget vs previous year actuals for each product group in respective to their country. This should be done as per the year to date figures. As you can see, my data does not have a calendar (day month/year) for each sales. Is this necessary? If so, what are my options?
| Posting Period | Current Year Actual | Current Year Budget | Previous Year Actual |
| 1 | 10,000 | 9,000 | 8,000 |
| 2 | 7,000 | 8,000 | 8,000 |
| 3 | 6,000 | 5,000 | 7,000 |
| 4 | 6,000 | 6,000 | 5,000 |
| 5 | 4,000 | 7,000 | 5,000 |
| 6 | 12,000 | 10,000 | 11,000 |
| 7 | 13,000 | 12,000 | 10,000 |
| 8 | 9,000 | 6,000 | 7,000 |
| 9 | 7,000 | 5,000 | 8,000 |
| 10 | 5,000 | 6,000 | 5,000 |
| 11 | 8,000 | 7,000 | 5,000 |
| 12 | 10,000 | 7,000 | 5,000 |
I'm currently trying to figure out how I can calculate the year to date values for each criteria (CY actuals vs CY budget vs PY actuals). With this, I want to summarise the data for each country/product group. I would really appreciate if you could help. Many thanks.
5 Replies
Hi Rayyy12 to do analysis in Power BI by date you will need Date table. Nevertheless, if you have possibilities to connect SAP and Power BI maybe best alternative to you. Check link https://learn.microsoft.com/en-us/power-query/connectors/sap-bw/message-setup-and-connect Hope this help
Hi some_bih thank you for your answer. Since I don't have a date column (DD/MM/YYYY), would my date table be different? I would appreciate some advice on this. The only date indicator in my dataset would be the months as mentioned above.
Moreover, due to certain difficulties I won't be able to connect to SAP directly (though I wish I could but it's not in my control). So that leaves me with the only option of extracting it to Excel and then to Power BI.
Hi Rayyy12 I understand your situation - I am using SAP in the same way :), without Budget functionallity, only actuals. I checked yesterday if there is column in format DD/MM/YYYY is SAP, but no luck. Still, there is posting period (month) and year. Go to your SAP table and "bring" year and month columns into your Excel (except you do not need year). The question arise: how you insert budget into SAP, hope not manually so somewhere you already have date7s columns?
some_bih Yes, I agree. Currently my dataset looks exactly like the example above (only the countries and products are missing), so my budget is extracted from SAP where I also get my actuals and prior year values. So I'm figuring out how I can calculate the year to date for each of these 3 criterias to make a good comparison. I hope that explains it.
Hi Rayyy12 from my experience use details as much as possible on both side (actual and budget) as it is easier to summarize higher level than allocate sum to part whatever logic is in place. I am writing this before you go to some solution in Excel / Power BI and make sure your stakeholders are aware your inputs concerning granularity of data.
