Forum Discussion
kent_n
3 years agoFrequent Visitor
month range combines with time range
Hi Im an absolute novice and tried google with no luck. Completely failed my first try to explain my issue (thanx daXtreme :), hopefully this might explain what I want to achieve. The foll...
- 3 years ago
Subvention & Grid Compensation = // To get what you want you have to // iterate over Date and Timme and // take action accordingly. // The dimensions should be connected // by 1->>-* to the fact table. fixed_prices // is a stand-alone table without any connections. // // Then you can write something like: var SwitchMonthNumbers = { 1, -- jan 2, -- feb 3, -- mar 11, -- nov 12 -- dec } var Output = sumx( summarize( Energy_sales_price, date_table[Date], // Make sure you've got MonthNumbers // in date_table. date_table[MonthNumber], hour_table[Timme], hour_table[High_low_hour] ), var CurrentMonthNumber = date_table[MonthNumber] var TotalProduction = calculate( SUM( Energy_sales_price[Production] ) ) var HighLowProfit = if( hour_table[High_low_hour] = "High", "Profit_high", "Profit_low" ) var FixedPriceTableFilter = { "Elsubvention", if( CurrentMonthNumber in SwitchMonthNumbers, HighLowProfit, "Profit_low" ) } var FixedPrice = SUMX( FILTER( fixed_prices, fixed_prices[Pristabell] in FixedPriceTableFilter ), fixed_prices[öre/KwH (ink moms)] ) * TotalProduction / 100 return FixedPrice ) return OutputSomething like this. I did not do any optimization on this. Wrote it without any real data and model. Might work... might not. Try to adjust it to your model.
daXtreme
Solution Sage
3 years agoSubvention & Grid Compensation =
// To get what you want you have to
// iterate over Date and Timme and
// take action accordingly.
// The dimensions should be connected
// by 1->>-* to the fact table. fixed_prices
// is a stand-alone table without any connections.
//
// Then you can write something like:
var SwitchMonthNumbers =
{
1, -- jan
2, -- feb
3, -- mar
11, -- nov
12 -- dec
}
var Output =
sumx(
summarize(
Energy_sales_price,
date_table[Date],
// Make sure you've got MonthNumbers
// in date_table.
date_table[MonthNumber],
hour_table[Timme],
hour_table[High_low_hour]
),
var CurrentMonthNumber = date_table[MonthNumber]
var TotalProduction =
calculate(
SUM( Energy_sales_price[Production] )
)
var HighLowProfit =
if( hour_table[High_low_hour] = "High",
"Profit_high",
"Profit_low"
)
var FixedPriceTableFilter =
{
"Elsubvention",
if(
CurrentMonthNumber in SwitchMonthNumbers,
HighLowProfit,
"Profit_low"
)
}
var FixedPrice =
SUMX(
FILTER(
fixed_prices,
fixed_prices[Pristabell] in FixedPriceTableFilter
),
fixed_prices[öre/KwH (ink moms)]
)
* TotalProduction / 100
return
FixedPrice
)
return
Output
Something like this. I did not do any optimization on this. Wrote it without any real data and model. Might work... might not. Try to adjust it to your model.
kent_n
3 years agoFrequent Visitor
Sorry, eent through the whole year and double checked some vaulues.
I forgot that the conditions only apply to monday-friday! Saturday and sundays are always Low.
I´ve added a Column in my date_table named Day of week with monday = 0 and sunday = 6
I can´t figure out how to add this 😞