Do you really need the hourly data by day? From what I understand so far, this is not needed. I suggest you group by day and channel and sum the Value column. That way you have 1 number by channel by day.
I have created a summary table using DAX. You should load your data using Power Query so it is in summary format (not by hour) and not use what I have done. I have only done this because I don't have access to your source data.
Once I created the summary table, I copied my formula and applied the table name changes. There was a missing aggregator which I have fixed. It seems to work.
Here is the file
https://www.dropbox.com/s/t6p9f6mtwu12y2g/Datafile2.pbix?dl=0
Here is the corrected formula
test =
CALCULATE (
CALCULATE (
( SUM ( Summary[qty] ) - MAX ( RatesTable[USAGE FROM] ) )
* MAX ( RatesTable[RATES] )
+ MAX ( RatesTable[$Amount] ),
FILTER (
RatesTable,
SUM ( Summary[qty] ) < RatesTable[USAGE TO]
&& SUM ( Summary[qty] ) >= RatesTable[USAGE FROM]
),
FILTER (
RatesTable,
MAX ( Summary[Date] ) < RatesTable[DATE TO]
&& MAX ( Summary[date] ) >= RatesTable[DATE FROM]
)
),
FILTER (
ALL ( Calendar ),
Calendar[MonthID] = MAX ( Calendar[MonthID] )
&& Calendar[Date] <= MAX ( Calendar[Date] )
)
)
* Matt is an 8 times Microsoft MVP (Power BI) and author of the
Power BI Book Supercharge Power BI.
