Average of first month as baseline

[Anonymous]

Hey there,

assume, I have a table Testdata with Products and Regions and prices changing every month. So, it would look like this

Region	Product	Date	Price
A	1A	01.01.2023	1
B	1A	01.01.2023	1.6
A	2B	01.01.2023	2.8
B	2B	01.01.2023	3
A	1A	01.02.2023	1.5
B	1A	01.02.2023	2.1
...

I want to show the developement of average prices per product over the time. I usually create date-dimension-tables based on my data ranging from startofyear(Testdata[Date]) to endofyear(Testdata[Date]) in my datamodels.

Developement of average prices should be displayed absolutely (no problem), but also relatively. Therefore, I would like to have a measure calculating the average price in the first month of my data, no matter wich month is displayed.

I tried:

AV_Start = CALCULATE(AVERAGE(testdata[price]), Filter(ALLSELECTED(testdata[Date]), testdata[Date]=MIN(Testdaten[Date])))

and several similar statements. But values keep changing over my timeline, which I don't want them to.

Could anybody help?

(I will be online on monday again, so don't bother if, in the meantime, I won't answer if your suggestions have worked .)

[Anonymous]

Hey there,

thanks for your replies! Unfortunately, I failed in loading up testdata this morning.

I tried your solution, Bea, but it didn't work. Seems logical to me, but doesn't turn up with the expected result.

But this did work:

AV_Start = CALCULATE(

Average(Testdata[Price]),

FILTER(

ALLEXCEPT(Testdata, Testdata[Product]),

Testdata[Date]=Min(Datedimension[Date])

)

)

But it only worked by using the date-hierarchy from the testdata-table in the visual, not the hierarchy from the datedimension-table.

Another formula that also worked with the date-dimension-table is:

AV_Start= CALULATE(

AVERAGE(tesdata[price]),

DATEADD(

datedimension[date]),

DATEDIFF(MIN(testdata[date]),

CALCULATE(MIN(datedimension[date]), all(datedimension)),

MONTH

),

MONTH

)

)

[BeaBF]

@Anonymous Try with:

AV_Start =
CALCULATE(
AVERAGE(Testdata[Price]),
FILTER(
ALL(Testdata[Date]),
Testdata[Date] = MIN(Testdata[Date])
)
)

BBF

[lbendlin]

Please provide sample data that covers your issue or question completely.

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...