calculate static value with filter

[lpd82]

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!

[v-yetao1-msft]

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

[v-yetao1-msft]

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.

[lpd82]

ah; sorry...  I added the data table for clarity.

[v-yetao1-msft]

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.