I have a table with half hourly actuals for electricity produced, dating back to the start of the year. In addition to this i have corresponding half hourly forecasts in a separate table and then additional tables for a half hourly electricity price if within a tolerance level of the forecast and a different table with a different price if outside of that tolerance. I also have a date table broken down into half hourly periods (on the hour and half past the hour every day for the year). All tables are linked by the half hour period (i.e 18/07/2018 14:30:00) How can i create measures to calculate at the half hourly level and then correctly sum at a daily/monthly level? I want all revenue calculations done at a half hourly level (due to the possibility that within a half hour period it may be out of tolerance and therefore need to use the out of tolerance price, but the day as a total may be in tolerance and I want to therefore avoid the sum of the days output all being multiplied by the in tolerance price) and once half hourly revenue is calculated I can then sum to analyse at a daily and monthly revenue.
Thanks in advance.
Relationships and example table are above. All tables are linked via a 1-1 relationship for the DateTime (i.e. 19/07/2018 14:30:00). Revenue needs to be calculated half hourly which then needs to be summed to give a total for the day and month. For the revenue, if the actuals are within a % threshold of the forecast, then the N2EX price for that 30 min period is used (also the same if below threshold). If, however, the actuals are above the threshold, the N2EX price is used for the portion within threshold and the Cashout Index is used for the portion over the threshold. I have a measure which correctly calculates it within the half hourly period, but this is not working at the total level (as the total actuals are within threshold of the total forecast, but there are individual half hour periods which are not, hence why I need a revenue calculation at half hourly level to then all sum to a total). My issue may arise from my revenue calculation using the SUMX function for actuals, forecast, N2EX price and SSP price. This works if displaying at a half hourly level, but at a total level it is obviously summing everything up (including prices) first, which I do not want to happen. I don't, however, know how else to get around the error of identifying a unique value from the column when writing the measure, without using SUMX.