Line Chart for tracking Market Share

[TomR726]

I am having trouble figuring out a way to accurately track market share over time on a line chart. Currently, I have my source data as an excel spreadsheet which I receive information for on market share, which is very simply "Total Machines of company X divided by Total Machines at site Y". This gets updated periodically as I find out new information on different sites, so every few days the market share % may change slightly. 

Here is an example of the excel spreadsheet which provides the source for my Power BI, and the market share I am basing this off:

When I created the line chart, I wanted to track these over time, so i used the following DAX formula:

DateTable = CALENDAR(DATE(2023,8, 10), TODAY() -1)

I am guessing that based on the date I started tracking (August 10th, 2023) that this would update to the prior day everytime I refreshed my data from any updates that happened in the excel source file. However, I have something that ended up looking like this:

As you can see, the line chart only picks up on the last update from the excel sheet (source), and does not track the progression over time. Is this perhaps something wrong with my code, my excel sheet source, or both? I have tried a few options and keep ending up at the same spot. I can provide any other information, but I did not want to put too much where it is confusing.

Any help would be greatly appreciated, thanks!

[parry2k]

@TomR726 you will have a date column and when each transaction happens it will have a date when it happened, not multiple date columns, but one date column with the value when the actual event/transaction happened. In your example, you will still have 3 rows and each row will have a date next to it, you table will have 3 rows, 4 columns 

[parry2k]

@TomR726 if you don't have the date column at the source, Power BI will not able to generate it by itself. That's it. You need to bring the date column to your source to make it work. 

[TomR726]

Fair enough, I can look in to trying to add that in despite the manual issue. One last question, when I add that in and change it over time- Say I make a change august 22nd that takes market share from 73.3% to 61.1%, then another change August 28th going from 61.1% back to 73.3% because I found out that area does not apply. The last modified date in the source excel spreadsheet would be August 28th, so would I need multiple columns that shows every date change, or just the most recent? i feel like I may be getting in to the exact same problem with that if its kept in one column unless I am misunderstanding

[parry2k]

@TomR726 you are talking about the dates, where do you have date in your sample data? I don't see it anywhere, how someone will know when you are talking about dates what does that even mean in context to the sample data? Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[TomR726]

So there are no dates in the excel source spreadsheet, which is why I did not post them. The excel source spreadsheet only contains market share data. The reason for this is that there are hundreds to thousands of rows for each country, and it would not be feasible (at least not practical at this time) to have a separate column with a date updated everytime the corresponding cells for the market share calculation was updated in excel.

That all being said, there are dates in the x-axis shown in my power BI line chart screenshot. My idea was to refresh this file daily, in hopes of seeing the updated changes from excel, and tracking them over time. So to reference my previous example, if the market share was 73.3% for a country when I started tracking it on august 10th, and the market share dropped down to 61.1% on August 22nd, I would expect to see something like this:

I guess I can try to boil it down to:

• Is this graph above possible to recreate based on a Power BI dax formula and me refreshing the file daily to capture the current market share on that day from the source file, then track it as such in the picture above? Can it be done without actual dates in the Excel source file?
• If not, do I have to create a separate column in my excel spreadsheet with dates? And if so, what happens when I refresh the date? would it render the previous date useless and mess up the chart?

[parry2k]

@TomR726 can you provide sample data with the expected output, it is not clear what you are trying to achieve. Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[TomR726]

Sure thing, I can provide additional data:

So using the information above, we see that Ghana has a market share of 73.3% (22 machines out of a total 30). 

So when I started tracking this information (August 10th), this is the info that I had to establish the market share. The line chart shows 73.3%, in line with what is in the source excel spreadsheet

Now suppose I find out some new information on August 22nd, and that is the day I update my excel spreadsheet that feeds power BI. Lets say its a new area I didnt know about, and therefore the market share calculation would change:

Now my market share is only 61.1%, once we take in to account the new site. However, when I update the information and refresh power BI, here is what I see:

Instead of a drop from 73.3% to 61.1% market share on August 22nd, I simply see the new calculation in a straight line. I'm hoping to track these changes over time so we can see the rise/fall of market share, so unfortunately this chart is just updating to the most recent market share % with a completely straight line. 

I hope that helps, I dont know what other data you may need, but please let me know if there is anything else and I can provide it.

Thanks!