Another question on totals

Hi lcerrapa,

Please modify the formula for [Stock (t)] and create an extra measure based on [Stock(t)].

Stock (t) =
VAR startDate =
    CALCULATE ( MIN ( 'calendar table'[Date] ) )
VAR searchDate =
    CALCULATE (
        MAX ( FactTable[Date] ),
        FILTER (
            ALLEXCEPT ( FactTable, FactTable[Material], FactTable[Store] ),
            FactTable[Date] < startDate
        )
    )
RETURN
    CALCULATE (
        SUM ( FactTable[Quantity] ),
        ALL ( 'calendar table' ),
        FactTable[Date] = searchDate
    )

stock total = SUMX(VALUES(FactTable),[Stock (t)])

Best regards,

Yuliana Gu

Hi lcerrapa ,

---

If I remove this relationship I get the totals you told me. I have two questions about this:

1.- Is possible to get the totals with this relationship?

2.- From design point of view, I always saw this relationship active. Is a better solution not to have this relationship in some cases?

---

No. In that case, you have to remove this relationship. If there existing a relationship, it would filter out those records from fact data table where dates aren't matched with the selected date in slicer. That makes it unable to fetch the corresponding values, whose corresponding dates are before the selected date. 

In short, in your scenario, removing this relationship makes sense.

Regards,

Yuliana Gu