Forum Discussion
Helper Icalculate static value with filter
Howdy!
How do I formulate "InitialQTY" to yield the result below with filter Data="Current" applied? There are other meta data the table such as locations, so I want the "InitialQTY" to summarize based on the dynamic field selections.
Data table:
| Mat | Data | Location | CreateDate | QTY | InititalQTY |
| 1 | Current | B | 1/18/2022 | 100,000 | 100,000 |
| 1 | Current | A | 1/18/2022 | 856,800 | 856,800 |
| 1 | Hist | B | 1/14/2022 | 100,000 | 100,000 |
| 1 | Hist | A | 1/14/2022 | 1,391,200 | 1,391,200 |
| 1 | Hist | B | 1/7/2022 | 100,000 | 100,000 |
| 1 | Hist | A | 1/7/2022 | 1,391,200 | 1,391,200 |
| 1 | Hist | B | 12/31/2021 | 100,000 | 100,000 |
| 1 | Hist | A | 12/31/2021 | 1,391,200 | 1,391,200 |
Without filter:
| Mat | Data | CreateDate | QTY | InititalQTY |
| 1 | Current | 1/18/2022 | 956,800 | 1,491,200 |
| 1 | Hist | 1/14/2022 | 1,491,200 | 1,491,200 |
| 1 | Hist | 1/7/2022 | 1,491,200 | 1,491,200 |
| 1 | Hist | 12/31/2021 | 1,491,200 | 1,491,200 |
With filter: Data="Current"
Result Example 1
| Mat | Data | CreateDate | QTY | InititalQTY |
| 1 | Current | 1/18/2022 | 956,800 | 1,491,200 |
Result Example 2 with Location added
| Mat | Data | Location | CreateDate | QTY | InititalQTY |
| 1 | Current | a | 1/18/2022 | 856,800 | 1,391,200 |
| 1 | Current | b | 1/18/2022 | 100,000 | 100,000 |
THANKS!
6 Replies
Hi lpd82
I still have doubts that when you select data is current , what are the calculation rules of InititalQTY? From the sample you provided, I can't calculate the InitialQTY in result 2. Can you provide a complete sample and describe the calculation logic of your InitialQTY in detail ?
Best Regard
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
lpd82ah; sorry... I added the data table for clarity.
Hi lpd82
Your need is not clear , is “InititalQTY” the result you want ? When you choose “current” , what is the result you want ? Summarize the value of field [InititalQTY] ?
If yes ,create a measure like that :
Measure = CALCULATE(SUM('Table'[InititalQTY]),ALLEXCEPT('Table','Table'[Data]))If no , please provide detailed requirements and results .
Best Regard
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- lpd82
Ailsa,
Thanks for responding. Yes, [initialqty] is the result I want. When I select data is current, I want result to look like "Result example 1" or "Result example 2" when location is added to the table.
Hi lpd82
I created a sample with the data you provided . For the value of InititalQTY you returned above result1 and result 2, I don't quite understand your calculation logic, you can take a look at my sample.
Create a measure to sum the QTY by 'Table'[Data] and 'Table'[CreateDate] .
InititalQTY = CALCULATE(SUM('Table'[QTY]),ALLEXCEPT('Table','Table'[Data],'Table'[CreateDate]))Then add a slicer to filter 'Table'[Data] .
The final result is as shown :
I have attached my pbix file , you can refer to it .
Best Regard
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- lpd82
Ailsa - I appreciate you're working on this. When I filter "current"; the initital date dropped off the table and yield the incorrect value. I want the values from 12/31 as the initital value always. The initial date is dynamic, so I cannot hard code 12/31. In otherwords, 1,491,200 should always be the subtal for current.
thanks
