I work in a power company where I need to calculate the variance between the planned production and the actual production of power stations. My report consists of four tables:
- Timetable (containing date and time details)
- Actual Production (with actual production values, specific times, and power station IDs)
- Planned Production (including planned production values, times, and different power station IDs)
- Power Station Overview (linking power station names to corresponding IDs).
My goal is to calculate the total variance between actual and planned production for each hour of the day. However, the actual production values arrive at different times, causing incorrect variance values throughout the day. To address this issue, I attempted to use two measures described below.
LastDataArrival =
MAX('actualProduction'[TIME])
And
ProductionDifference =
IF (
NOT ISBLANK(
CALCULATE (
SUM('actualProduction'[actualProductionValue]),
FILTER('actualProduction', 'actualProduction'[DATE] = [LastDataArrival])
)
),
SUM('actualProduction'[actualProductionValue]) - SUM('productionplan'[prodplanValue]) ,
BLANK()
)
Although this approach worked initially, the difference value became distorted when a new actual production value arrived. For example, if the planned production for a particular hour was 100 and a data point with a value of 2 arrived for one power station, the difference became 98. While the table displays the difference correctly for each power station when selected in the slicer, it fails to provide accurate results when viewing all power stations simultaneously.
I am seeking a solution to calculate the sum of differences only for rows that have both actual production values and corresponding planned production values. As I am relatively new to Power BI and DAX, I may be overlooking a straightforward solution. Any guidance or suggestions would be greatly appreciated.
