Forum Discussion

RobinIrving's avatar
RobinIrving
Frequent Visitor
1 year ago

Tables - incomplete months - calculation issues

Hello,    Ive done lots of searching and used copilot a lot but still cant quite get there. Apologies for the long post but its not easy to explain.    I am pulling in a table of forecast hours f...
Need Help
danextian's avatar
danextian
Icon for Super User rankSuper User
1 year ago

Hi RobinIrving 

It would be easiser for us to understand what you're trying to achieve if you provided a workable sample data (not an image), your expected result from that same data and the reasoning behind. You may post a link to a sanitized copy your pbix or an excel file stored in the cloud.

 

That aside, here's the clude why they don't appear even if you add zero or use colesce

This works fine for all resources however anyone with no rows for a month still show up as blank in my matrix

because you can't assign a value to a row that doesn't exist.  So if employee A has data only unitl June and you use the same column, employee A will show data only until June. This is one of the reasons why we use a separate dimensions table containing a complete set of dates (even the future ones) so values can be assigned to them. Otherwise, you will have to append some dummy future dated rows for each employee into your data just to make up for the missing ones which is another way to slow down data refresh.

  • RobinIrving's avatar
    RobinIrving
    Frequent Visitor
    1 year ago

    Thanks both. The problem is this is for my work and they dont allow me to mail to my own email so not sure how I could do this even if I cleanse the data prior to doing so. I dont have Desktop for personal use only at work so I cant create a 'dummy' PBIX either easily.  I think I might use the add dummy appended rows and I am working on that now. My data is not huge less than 1000 rows and its only refreshed a few times per week - so adding a few seconds onto that is not something that will be an issue. If that doesnt work I shall come back and work out how to create some dummy data to show exactly what I am trying to do. 

     

    Sorry its rather complex to explain and yes totally understand why a cleanes PBIX would make any question like this much easier to solve. Thanks all anyway !!

  • RobinIrving's avatar
    RobinIrving
    Frequent Visitor
    1 year ago

    Thanks for mentioning the dummy option - I managed to get that working well with a bit of fiddling. With this level of data it works fine.If I ever need anything more I will worl out a way to be able to provide a cleansed PBIX for you all to look at. Cheers

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Hi RobinIrving,

      In fact, You only need to remove the sensitive data from the table records or tried to replace them to dummy values.(e.g. abc, test123)
      Regards,

      Xiaoxin Sheng

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Hi RobinIrving,

        Any update on this? 

        Regards,

        Xiaoxin Sheng