topic Average of first month as baseline in DAX Commands and Tips

Average of first month as baseline

Anonymous — Thu, 14 Dec 2023 15:06:01 GMT

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 .)

Re: Average of first month as baseline

lbendlin — Mon, 18 Dec 2023 00:14:27 GMT

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-Forum/ba-p/963216

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/1447523

Re: Average of first month as baseline

BeaBF — Mon, 18 Dec 2023 09:50:16 GMT

Anonymous Try with:

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

BBF

Re: Average of first month as baseline

Anonymous — Mon, 18 Dec 2023 10:23:53 GMT

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

)