I think I'm missing an ALL() somewhere.

[alfranco17]

Hi.

I'm trying to get the value of non-expired inventory for a period.

This is the expected result:

And this is what I get with the relationship. As soon as I connect Periods to Inventory (there are several measures that require it, and this non-expired was kind of a latter request) I get only one month.:

I believe I'm missing an ALL() somewhere, but I am really stuck.

Would you please help?

No relationship
With relationship

Available CALCULATE :=
VAR __PeriodStart =
    MIN ( Months[Start] )
VAR __PeriodEnd =
    MAX ( Months[Start] )
RETURN
    CALCULATE (
        SUM ( Inventory[Quantity] ),
        FILTER (
            Inventory,
            AND (
                Inventory[Valid from] <= __PeriodEnd,
                Inventory[Valid to] >= __PeriodStart
            )
        )
    )

Or this one:

Available SUMX:=VAR __PeriodStart =
    MIN ( Months[Start] )
VAR __PeriodEnd =
    MAX ( Months[End] )
RETURN
SUMX(
        FILTER (
            Inventory,
            AND(Inventory[Valid from] <= __PeriodEnd,
                Inventory[Valid to] >= __PeriodStart)
        ),
        Inventory[Quantity]
    )

But as soon as I define a relationship, it only gets that one month:

Thanks.
Armando.

 

[Alex Blakenburg]

I don’t know if there is a way of doing it using just DAX, but my thoughts would be to blow out inventory table date range so that you have a row for each month and then using that in your data model. You can do that using Power Query, instructions can be found here > Generating Rows by Month for Date Ranges

 

[alfranco17]

I don’t know if there is a way of doing it using just DAX, but my thoughts would be to blow out inventory table date range so that you have a row for each month and then using that in your data model. You can do that using Power Query, instructions can be found here > Generating Rows by Month for Date Ranges

Thanks, it's a great idea. I'll give it a try.

 

[Alex Blakenburg]

Let us know how you go.