Hi,
I am new to Power BI but have been able to create a few reports - learning on my own (by making mistakes).
I have come across something I need help with.
I have watched almost all of the videos that cover actual vs budget topic. I am not able to resolve the issue that I’m having. I am not sure why.
So what I would like to have in PBI as a visual is following (made up numbers):
Formulas used:
Plan Total = sum(‘Test Table for Goals 2’[Plan])
Days in Year = CALCULATE(COUNTROWS(‘Date’), ALLEXCEPT(‘Date’, ‘Date’[Year]))
I’m not sure why I’m not getting 365 days as I saw in one of the videos rather I’m getting 5114 days (I filtered Year 2021 only for the page). Also, I don’t see anything in Actual column when I add that to the report next to “Actual Date” column in the report.
How can I get to what I’m trying to accomplish:
Actuals Column: by month
Plan Column: by month
Forecast Column: by month
Visual as shown
A note: actual numbers are count of column that has data in text format. Example: month of July actual numbers let’s say 500 is a count of total names in a column. I thought I just mention that it is not sales number (number format) rather it’s a count of text rows by month.
I actually just conducted a session on this at last week’s Enterprise DNA Analytics Summit:
that covers your use case exactly. Give this a watch, and if you still have questions or problems then please post your PBIX work in progress, and I’ll be happy to work through a solution with you.
Thank you for the response. No, it didn’t help. It actually caused issues with the existing table when I joined the date in date table and the date created in the budget table. I think there’s some issue with the date. I am using the M code date table that Melissa created.
@BrianJ I don’t have any relationships in my Actuals (factual) table and the Excel file with Plans (in month and year). Could that be the reason that it’s not working? Every time I try to bring date from Dates table to see the actuals for specific date, it doesn’t work. I tried adding the relationship with the Dates table and the Excel file (Plans table) after adding the granular level dates, it doesnt’ give me anything.
My sincere apologies for the delay in responding. I’ve been working on a few similar data modeling/granularity questions and solutions, and didn’t realize this one was still open. It’s impossible for me to diagnose what’s causing the problem above just from your description. However, if you could please provide me a copy of your PBIX work-in-progress file, I think it should be pretty straightforwar to track where things are going sidewise and how to fix it.
I was able to get help resolving it. It was simpler than I thought. I just needed to add a formula for date to change from being time stamped as it was coming from our database to extract date without time stamped so to build relationship with file that had our planned numbers.
Now I’m working on figuring out how to graph forecast and projection along with actuals. If you can provide direction, that would be great.
Again, thank you for making sure I get the help I was looking for.
Great - glad you were able to get that resolved. Sorry for my slow reply - between coordinating all the content we’re rolling out these days, getting the next lesson for Accelerator set up and closing out Challenge #17, I just haven’t been able to respond to nearly as many forum questions as I usually do.
Given that this thread has been solved, I would recommend you post your new question in a separate new thread, since many of the people who respond to forum questions only look at open threads.
