Inv Summary (W3)(JUL26)
Updating weekly in our Power BI Dashboard turned out to become harder than we expected as updating values on existing tables isn’t user friendly at all compared with updating our data in Excel. We can replace values in our existing table, but apparently it’s fixed as the whole column, not for a particular field. In addition, existing data tables in Power BI are static, but our Excel data is dynamic. Therefore, this creates a hindrance as our Funds are dynamic, not static. The only way our tables are static is when we snapshot for the week. However, this also means that W1, W2, W3, W4 are all separate and independent tables according to Power BI. In Excel, this can easily be done in a pivot table, or using vlookups if we break out the weekly data into different sections.
Another syntax issue that we encountered was when certain Funds were liquidated for the week, therefore, showing Qty as 0, and causes a calculation error for APP as Qty cannot be 0. When this happens, this creates a formatting error as error isn’t recognized as a number, and therefore, all values in the column is forced converted to text format, which is incorrect. Values won’t calculate if it’s recognized as a text format. Therefore, we forced edit the values as 0, then updated the type back to decimal numbers and so on. Once again, this method isn’t user friendly.
As for logic errors, once we created W3 table, we had to update our DAX measure to reflect Type calculations, otherwise, would pick up from the previous table. Power BI doesn’t update their tables automatically, so updating the DAX measures to reflect the new table isn’t user friendly either. Furthermore, every table we add for the week, we would need to update a few other DAX measures on the new table. Not efficient for our purposes. This doesn’t add much value either, so we would need to revisit this for next month.
However, we managed to create a new table Inv_APP_JUL26 in Power BI. We’ve consolidated APP for Weeks 1, 2, 3. We believe that once W4 finishes, we can either create a new data table and add column, or create a new table as W4. Again, this creates a hindrance as we can’t just add column as APP is an input column copied from our Excel dashboard, and other tables calculates APP as DAX measures for their respective tables. Week 4 will be our final test to see if we can automate our dashboard seamlessly when updating our financials weekly, or if it creates a hindrance, then we’ll consider updating monthly instead.
