use LAG()? I tried to use your picture to create a table, but SSMS didn't know what to do with the picture when I tried to paste it in.

In all...

After fiddling with this for what seems like forever, I finally got most of the way there. The PowerQuery transform works, but the data type transformations do not.  I inserted...

Okay, I got it to work sort of.   Had to resort to using Excel to run the PowerQuery on a folder source, filter for PDFs I want, and then once...

• This reply was modified 1 year, 4 months ago by pietlinden.
• This reply was modified 1 year, 4 months ago by pietlinden.

I think I understand how to do it now. Running totals of Sales Amount (Gross Revenue) is straightforward. With the SCD2 of Product, I can store the unit cost of...

I haven't touched SSRS in forever  (looks in book). You *might* be able to use RunningValue(), but I would do this in T-SQL as it's much easier to do there....

• This reply was modified 1 year, 4 months ago by pietlinden.

Phil,

Not sure I totally answered your question before... To make a long story short(er), I'm basically trying to see/analyze/understand how profitable the business is over time (with the crazy grain...

This design works assuming that you may want to add the costs of a product's ingredients, but only ever want to aggregate the selling price of products by using an...

but only ever want to aggregate the selling price of products by using an average across time...or the latest value which is similar to what you would have done if...

• This reply was modified 1 year, 4 months ago by pietlinden.

Martin,

I think I understand your answer. (Took a couple days!) Basically, the ProductIngredient table (ProductID, IngredientID, Weight...) factless fact handles the slowly changing dimension by pointing each new version of...

Martin,

Oh, like thaaaaaat!!!  That's a lot to digest, but I have my Ralph Kimball secret decoder ring working. Makes sense!  now to figure out how to create a table function...

Wow, Martin! Thanks! I understand most of it. The part I was waaaay wrong on is that YES, the changing prices DO get somewhat merged into the "Recipe"/ProductIngredient table.  (I...

Eirikur,

(Oh wait, before I go too far, all of this mess is in DAX/PowerBI...  "Run away, run away!" --King Arthur)

What do you mean by Scopes? ISINSCOPE() kinds of scopes?  the...

There's a Sales fact, but that part is easy, once all the dimension stuff is fixed, so I left it out.  Product is related to Sales too. I'm just having...

I always think of EXISTS as being useful for testing if one table has ANY related records in another. (So the exact records in the related table aren't important to...

At least the cert exams when I did them forced me to learn about and use features I didn't know about. That part was super useful.