Search code examples
sql-serverssasdax

Point-in-time reporting in a DAX model

My current organisation has the nasty habit of reusing product codes. This means what was say, a jumper, two years ago is now a pair of shoes.

The table these are held in is a type 2 SCD, let's call this table 'Items' in my DAX model (I'm fairly new to DAX), e.g.

Item code | Description      | Valid From    |  Valid To
1000      | Jumper           | 2016-01-01    |  2016-06-01
1000      | Shoes            | 2016-06-02    |  2016-12-31

There is a sale table, which only has the item code in it, a quantity, value of the sale and a date, called 'Sales' in the DAX model:

Item Code | SaleDate         |  Quantity     | Value
1000      | 2016-01-31       |  1            | 25.00
1000      | 2016-02-29       |  2            | 50.00
1000      | 2016-06-30       |  1            | 30.00
1000      | 2016-10-31       |  1            | 30.00

The desired out put therefore when querying the DAX model is as follows:

Item Code | Description |  Quantity (sum)  | Value (sum)
1000      | Jumper      |  3               | 75.00
1000      | Shoes       |  2               | 60.00

I believe this needs a calculation in the Sales table of my DAX model to link to the Items table, but struggling to come up with what I need. I've tried doing some reading around the subject (e.g. Alberto Ferrari) but clear explanations are not really there.

Structure-wise, the sale table has a lookup reference to a calendar table but the Items table currently has no reference to any tables (it does not have unique enough entries to be linked to the Sales table).

Would be grateful for any pointers/ideas.

Solution

  • Create two measures:

    Quantity Sum =
CALCULATE (
    SUM ( Sales[Quantity] ),
    FILTER (
        Sales,
        [SalesDate] >= MAX ( Items[Valid From] )
            && [SalesDate] <= MAX ( Items[Valid To] )
    )
)


    Sales Sum =
CALCULATE (
    SUM ( Sales[Value] ),
    FILTER (
        Sales,
        [SalesDate] >= MAX ( Items[Valid From] )
            && [SalesDate] <= MAX ( Items[Valid To] )
    )
)

    Then in your client you can use a table or any visualization to show the desired output by adding Item Code and Description from Items table and the measures created previously.

    This is the result in a Power BI matrix:

    enter image description here

    Let me know if this helps.