Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Quakker
Helper II
Helper II

DAX - weighted Average Calculation not working as desired

Hi all,

 

i am struggeling in Power BI Desktop to create a weighted average under consideration of Previous year values.

Sample Data:

 

LocationYearMonthConsumptionxCostxPriceTypeMonth_No
Berlin2021November1495335248,267420,03509772Gas11
Madrid2021November3390011,86491325,69610,02693964Gas11
Berlin2022November  0,0557557Gas11
Madrid2022November  0,04877443Gas11

 

The result i want to reach is the following:

If i dont have any values within the consumptionx and costx column i want to take the price and i want to multiply it with the previous year & month consumption to forecast the costs.

Quakker_2-1668619916537.png

 

So for example: In November 2022 i want the following result:

Quakker_1-1668619705385.png

Unfortunatly i receive the value in red whatever i do.

I need to calculate the green value?

I´d be very grateful if somebody has an idea how to solve it.

Best Regards.

 

1 ACCEPTED SOLUTION
v-jayw-msft
Community Support
Community Support

Hi @Quakker ,

 

Please check the formula:

Column = 
var lastyearmonth = CALCULATE(SUM('Table'[Consumptionx]),FILTER(ALLEXCEPT('Table','Table'[Location]),'Table'[Year]=EARLIER('Table'[Year])-1&&'Table'[Month]=EARLIER('Table'[Month])))
return
IF(ISBLANK('Table'[Consumptionx])&&ISBLANK('Table'[Costx]),lastyearmonth*'Table'[Price],'Table'[Consumptionx]*'Table'[Price])

vjaywmsft_0-1669970741604.png

 

 

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

View solution in original post

2 REPLIES 2
v-jayw-msft
Community Support
Community Support

Hi @Quakker ,

 

Please check the formula:

Column = 
var lastyearmonth = CALCULATE(SUM('Table'[Consumptionx]),FILTER(ALLEXCEPT('Table','Table'[Location]),'Table'[Year]=EARLIER('Table'[Year])-1&&'Table'[Month]=EARLIER('Table'[Month])))
return
IF(ISBLANK('Table'[Consumptionx])&&ISBLANK('Table'[Costx]),lastyearmonth*'Table'[Price],'Table'[Consumptionx]*'Table'[Price])

vjaywmsft_0-1669970741604.png

 

 

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
lbendlin
Super User
Super User

Why is the red value wrong?

 

I hope your data model includes a Kalender table?

 

CP = averagex(Abfrage1,[Price])*COALESCE(sum('Abfrage1'[Consumptionx]),CALCULATE(sum('Abfrage1'[Consumptionx]),SAMEPERIODLASTYEAR(Kalender[Date])))

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.