How to use value from filter in var when summarize a table

[BBart]

I have a table 'Blad1':

MPC	Unit	day	CostPrice
1084	STK	20211010	0.39
1180	STK	20211010	0.06
1220	NET	20211010	1.65
1084	1084	20210922	0.34
1180	1180	20210922	0.06
1220	1220	20210922	1.65
1084	1084	20210901	0.30
1180	1180	20210901	0.07
1220	1220	20210901	1.65

Then I devrived two tables from is which I will use as filters in order to dynamically compare two periods:

Date1 = DISTINCT(Blad1[day])

Date2 = DISTINCT(Blad1[day])

Then I want to use these filters to create a New Table as follows (without success):

New_Table =

VAR CurrentDate = CALCULATE(MAX(Blad1[day]),FILTER(Blad1, Blad1[day]=SELECTEDVALUE(Date1[day])))

VAR PreviousDate = CALCULATE(MAX(Blad1[day]),FILTER(Blad1, Blad1[day]=SELECTEDVALUE(Date2[day])))

RETURN

SUMMARIZE(

Blad1,

Blad1[MPC],Blad1[Unit],

"CurrentCostPrice", CALCULATE(AVERAGE(Blad1[CostPrice]),FILTER(Blad1, Blad1[day] = CurrentDate)),

"PreviousCostPrice", CALCULATE(AVERAGE(Blad1[CostPrice]),FILTER(Blad1, Blad1[day] = PreviousDate))

)

My expected output (filtering Date1[day]= 20211010 and Date2[day]=20210901) is:

MPC	Unit	CurrentCostPrice	PreviousCostPrice
1084	STK	0.39	0.30
1180	STK	0.06	0.07
1220	NET	1.65	1.65

Does anybody know how I can use these filtered values as VAR in the New_Table formula?

[v-jingzhang · Accepted solution]

Hi @BBart 

Calculated tables are populated when they are created or when you refresh the whole model. They cannot be dynamically changed by slicers or filters according to user interactions. To achieve your expected result, you need to create measures which can be affected by slicers and use a table visual to show their value in the report. 

Create measures 

CurrentCostPrice = 
VAR CurrentDate = SELECTEDVALUE(Date1[day],MAX(Date1[day]))
RETURN
CALCULATE(AVERAGE(Blad1[CostPrice]),ALL(Blad1[day]),Blad1[day] = CurrentDate)

PreviousCostPrice = 
VAR PreviousDate = SELECTEDVALUE(Date2[day],MAX(Date2[day]))
RETURN
CALCULATE(AVERAGE(Blad1[CostPrice]),ALL(Blad1[day]), Blad1[day] = PreviousDate)

You can download the attachment to see details. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[BBart]

@Greg_Deckler Sorry, I forgot to @ you :). I adjusted my questions and provided sample data and my expected outcome.. 

[Greg_Deckler]

@BBart Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[BBart]

Hi Greg,

I added some details. Hope this helps 🙂