Another question on totals

[lcerrapa]

Hello.

I found a lot of similar examples but nothing fits what I'm looking for. I hope you can help me. It's a very simple example. I have annotations of stock variations only (store, material, stock available). I need the stock available for a selected date on each store. Example: I have this FactTable and I select the following date: 14/04/2018

I defined a measure (wrong):

Stock (t) =
VAR startDate = CALCULATE(MIN('Calendar'[Date])) <-- Selected Date from a calendar, it may be only a month selection
VAR searchDate = CALCULATE(MAX(FactTable[Date]); ALL('Calendar'); FactTable[Date]<startDate) 

RETURN CALCULATE(SUM(FactTable[Quantity]); ALL('Calendar'); FactTable[Date] = searchDate)

I know the total I get is correct as it is defined but I'm a little blind and I don't know how to group the material and the store and get the right grand total. I get the total for the max date of the material, not material and store.

Thanks in advance

[v-yulgu-msft · Accepted solution]

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

[v-yulgu-msft · Accepted solution]

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

[lcerrapa]

Many thanks for your time @v-yulgu-msft

It doesn't work for me 

I defined the measure as you told me (Stock (t) and stock total). I added two more intermediate measures to check the dates in the Stock(t) measure, and both are correct.

I defined another measure combining both measures:

Total = IF(HASONEVALUE(FactTable[Store]); [Stock (t)]; [stock total])

And the last measure I think is the key to see the behavior:

_HasOneValue_FactTable_Store = HASONEVALUE(FactTable[Store])

Here is the output I get:

If there is no data for the selected date, the total measure doesn't show anything. In other way, if I select a date with information, the totals shows data but only but that date, not the grand total:

It only has value on the store 1003, not on 1002.

Could you help me wiht this issue?

Thanks!

I edit the reply. 

The difference is because I have a relationship between both tables:

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?

Thanks!

[v-yulgu-msft · Accepted solution]

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

[lcerrapa]

Thanks a lot, @v-yulgu-msft