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.
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!
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.
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
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.
ah; 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.
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.
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 |
---|---|
114 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |