cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Buzz1126 Regular Visitor
Regular Visitor

My auto weekly inventory calculation stopped working.

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.

4 REPLIES 4
Buzz1126 Regular Visitor
Regular Visitor

Re: My auto weekly inventory calculation stopped working.

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.

Community Support Team
Community Support Team

Re: My auto weekly inventory calculation stopped working.

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
Highlighted
Buzz1126 Regular Visitor
Regular Visitor

Re: My auto weekly inventory calculation stopped working.

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.

 

Capture 2.PNGCapture 4.PNG

Community Support Team
Community Support Team

Re: My auto weekly inventory calculation stopped working.

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (3,413)