I've made my best attempt to SUMX my data to get an accurate Profit and Loss (PnL) figure but I have been unsuccessful in writing a single measure that calculates the correct total amount when selecting multipule days and multipule zones.
I think my main problem is that my fact tables are at the hourly level and my calendar is daily. I have a seperate dim table for Hours (HE). I also am trying to slice and dice by zone (there are 5).
I tried using a SUMX for the hours and then a SUMX on the SUMX for the Zones and that fixed it for a single day…but when I select more than one day the sum skyrockets again. I’m sure there is a formula that incorporates additional Filters and Date logic but I haven’t been able to figure out the formula pattern L. Hoping someone can help with some syntax or point me in the right direction in the form of a specific example having to do with hourly calendar aggregations.