Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
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
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:
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!
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |