My auto weekly inventory calculation stopped working.

[Buzz1126]

I have a formula that automatically calcutes the change in inventory from one week to another.  This formula worked for several weeks until recently.  Our IT pushed an update to our Date Calendar (DimDate) that I believe affected the results to my formula below.  I'm stumped and can't figure out what happened.  My hope is that a second set of eyes can look at this and possibly figure out what is wrong.  

Note:  I believe it was switched from import mode to direct query.  I know for a fact it is currently in direct query mode.

The result should show ~30,000.  Instead it is showing ~300,000.  Our Dimdate calendar goes back to 2009, could it be that it is summing 10 years worth of inventory instead of just the previous weeks entered inventory?

Formula explanation:

-SUM() formula just sums the inventory quantity entered

- FiscalWeekofYear is a formatted decimal number 1-52.  The first week of January would be 1.

- FiscalYear is formatted as a decimal number also. It says 2019 or 2020 or 2018.

- FiscalDayofWeek is a number 1-7.  I want to grab the inventory entered on day "6"

- The inventory type is just grabbing a "daily" entered inventory vs a "monthly" entered inventory in case the days align.  Not that important.

- The MainProductName is the product type of the inventory I want to sum

Last Week Inventory Auto = CALCULATE(
    SUM(FactInventory[Inventory_Volume]),
    FILTER(ALL(DimDate), 
        DimDate[FiscalWeekOfYear] = SELECTEDVALUE(DimDate[FiscalWeekOfYear]) - 1 && //calendarweekofyear or FiscalWeekOfYear
        DimDate[FiscalYear] = SELECTEDVALUE(DimDate[FiscalYear]) &&
        DimDate[FiscalDayOfWeek] = 6),
        
    FILTER(
    	ALL(FactInventory),
    	FactInventory[Type] = "Daily" &&  
    	FactInventory[MainProductName]="Repairable 48x40")
)

Thank you in advance.

[v-shex-msft]

HI @Buzz1126 ,

Does this calendar table data structure changed during this update? How do these tables relationship mapping? (e.g. cross filter direction setting, relationship key, filter effects...)

Can you please share some fake data with the same data structure for test?

How to Get Your Question Answered Quickly

Regards,

Xiaoxin Sheng

[Buzz1126]

Hi @v-shex-msft 

Thank you for your reply.

Yes, when the data was switched over IT re-mapped the relationships.  There very well could be a problem there.  Building out test data might be difficult but would you mind if i just messaged you the actual file?  There's nothing really discrete about it but I would prefer not posting it to the internet.

Here is a screenshot of the relationships.

[v-shex-msft]

HI @Buzz1126 ,

>>There's nothing really discrete about it but I would prefer not posting it to the internet.

You do not need to share real data with us, you can try to create some fake data with same data structure to help us test on it.

According to your snapshot, your relationship seems broken, maybe you can consider adding a temp date bridge table with unique records to help correct mapping with different tables.

Regards,

Xiaoxin Sheng

[Buzz1126]

My first assumption was that this formula stopped working because of the switch from import mode to direct query.  I just had IT switch only the DimDate table back over to Import Mode, and the problem still remains.

So, now i have the DimDate table in import mode and the FactInventory and all other tables in  direct query.