Backlog by Location

[ekroll]

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

[SpartaBI]

@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]

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]

@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]

Yes, sorry, I was in the middle of adding some additional context when you replied to the first one.

So, I need to figure out cumulative backlog by store number, which is essentially:
(Any remaining pallets from previous day + 'Pallets Built'[PALLETS]) - 'Capacity'[Seasonal Capacity]

I've looked at the built in time functions and standard calendar related functions don't work for me as I need to use our company financial calendar and cannot rely on standard calendar dates aligning with our company's financial dates.

The 445_Calendar table I have in my model has all of the dates down to day-date that correspond to our company financial calendar.

[SpartaBI]

@ekroll they also have there patterns for fiscal calendars and custom calendars. Check them out and update

[ekroll]

I saw the custom time related calculations in there. I'll go throuh that and see if I can figure it out.

Thanks.

[ekroll]

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....