Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi all,
i am struggeling in Power BI Desktop to create a weighted average under consideration of Previous year values.
Sample Data:
Location | Year | Month | Consumptionx | Costx | Price | Type | Month_No |
Berlin | 2021 | November | 149533 | 5248,26742 | 0,03509772 | Gas | 11 |
Madrid | 2021 | November | 3390011,864 | 91325,6961 | 0,02693964 | Gas | 11 |
Berlin | 2022 | November | 0,0557557 | Gas | 11 | ||
Madrid | 2022 | November | 0,04877443 | Gas | 11 |
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.
So for example: In November 2022 i want the following result:
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.
Solved! Go to Solution.
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])
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])
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])))
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |