Forum Discussion

ekroll's avatar
ekroll
Frequent Visitor
4 years ago

Backlog by Location

Hi, 

I'm still pretty new to Power BI, DAX and such and am trying to figure out how to create a backlog by location.

I have 2 fact tables and a calendar table.

I've linked both fact tables up to the calendar table by date, and then created a calculated column to link the fact tables together:

 

Capacity will have a location, date and the daily capacity of that location (I'm using Seasonal Capacity):

 

The pallets built table has a location, date and the pallets built for the location:

I need to calculate the cumulative backlog by store and day, and am just not getting anywhere with figuring out how to approach this measure.

 

Any guidance would be appreciated.

Thanks

7 Replies

  • SpartaBI's avatar
    SpartaBI
    Community Champion
    4 years ago

    ekroll you need to delete the relationship between the fact tables (big no no) and create all the needed dimensions that can connect to both fact tables (just like the date). So DimStores and maybe more. I suggest go watch in SQLBI.COM the free introducion course for data modeling.

    • ekroll's avatar
      ekroll
      Frequent Visitor
      4 years ago

      I've updated my model so that I have a stores table as well:

       

      I followed a bunch of other disucussion regarding creating cumulative totals, and backlogs, but for the most part these are talking about things like counting open tickets and don't seem to need to separate by location as well as date.

       

      • SpartaBI's avatar
        SpartaBI
        Community Champion
        4 years ago

        ekroll by location you mean store?
        Any cummulative measure will be calculated for each store/location just by putting them on the filter.
        Regarding cummulative, do you mean runnning total, or in case of a back log, the last date?
        Also, I see you have some inactive relationships there. Make sure they are turned on. 
        I really suggest to do some reading about this. Regarding time intelligence you have a great resource here for most of the patterns including running total:
        https://www.daxpatterns.com/standard-time-related-calculations/

  • ekroll's avatar
    ekroll
    Frequent Visitor
    4 years ago

    Ok so I'm still unable to figure out the DAX to create a measure for backlog by date and store.

    I've gone through a bunch of the suggestions and it seems cumulative totals are pretty common, but not backlog, and even then, most are for a single item (like counting backlog of tickets).

    I've updated my model so I have 2 fact and 2 dim tables.

    Fact tables are for capacity by store and day and pallets build by store and day.

    Dim tables are my stores table and my financial dates table

    I'm really not understanding for some reason, how to create the cumulative backlog by day and store....

    My overall requirement is:

    If the store is in an over capacity situation (ie they have more pallets than capacity), then we must calculate the cumulative backlog until they dig themselves out of the hole.

    If the store is under capacity, the totals reset (ie you don't gain extra capacity... so if you have a capacity of 5/day and yesterday you only got 3 pallets, you don't suddenly have capacity for 7 pallets today).

    It's a pretty complex requirement, and my experience with DAX is still at a very biginner level unfortunately.

    I'd appreciate any help walking through a solution so that I can understand what's being done and why....