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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Rayyy12
Regular Visitor

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 PeriodCurrent Year ActualCurrent Year BudgetPrevious Year Actual
110,0009,0008,000
27,0008,0008,000
36,0005,0007,000
46,0006,0005,000
54,0007,0005,000
612,00010,00011,000
713,00012,00010,000
89,0006,0007,000
97,0005,0008,000
105,0006,0005,000
118,0007,0005,000
1210,0007,0005,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 5
some_bih
Super User
Super User

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? 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






some_bih
Super User
Super User

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors