Forum Discussion
Using ALL filter creates blank rows
Hi watje255_ju ,
Try replacing date from dimdate with date from factsales and format it as "Mmm yyyy"
Hi, thanks for your reply, that is the problem that I am trying to fix, when there is no data in the underlying table for that SKU and Month, I want to pull the average COGS per unit for that SKU, so that there is a COGS value and we can still calculate a gross margin. This happens in the odd occasion because the GL date can be 1 day behind the subledger date or vice versa, thank you!
- Hi watje255_ju part "to pull the average COGS per unit for that SKU" as there are no actuals for month December there is no average amount! In your table (marked with yellow above part" some details are from two tables, like fact and SKU. Why do you need table SKU ast there are just two values? I am not aware why, but the best would be to organize your model to have one fact table. Did you try to use SKU column in table, from Fact table? Also, having two fact tables, is not best practice as there is many to many relationships between fact and COGS table. Even, in this complex model (not according to best practice), the question is what average you want to get in case there is blank as current. I suggest to go to your model, granularity of data should be considered and create single fact table. I agree, the best would be to have data on transaction level. Still, if your workflow could not provide COGS data, you will need some alternative workaround to get some data (like average). Hope this help
Thanks, unfortunately the COGS data and the sales data comes from different systems so there has to be two fact tables. IF there is no data for the month due to timing mistmatch, I want to pull the average COGS for that SKU as we still need to report a GM for that sale. Thanks for your time.
watje255_ju you are on good way to resolve this. take a time
